How to create measure over Date Dimensions

Category: sql server analysisservices


JT59611 on Thu, 28 Dec 2017 23:04:28

sorry for the newbie question...

Got a tabular model.  1 Fact Record with Start Date=1/1/2017 and End Date 5/31/2017.  I want to create a measure that I can use to count that 1 fact record over a period of date dimension I use.  Please help... how do I formulate the measure?  Thank you very much.


willson yuan on Fri, 29 Dec 2017 07:45:20

Hi JT59611,

Thanks for your question.

Are you using SSAS tabular? If so, please create date table which only contain the first date in a month, something like below:
Date        YM( year(Date[Date])*100 + MONTH(Date[Date])) YearMonth = FORMAT(Date[Date],"YYYY-MMM")
1/1/2017    201701                                                             2017-Jan

Delete the relationship between this date table and your fact table:

Then you can create a measure as below:

CountOfRecords =
    FILTER (
        'Fact'[Start Date] <= MAX ( CalDate[Date] )
            && 'Fact'[End Date] >= MAX ( CalDate[Date] )


JT59611 on Fri, 29 Dec 2017 17:12:08

Hello Willson,

Thank you very much for that informative answer (and Happy New Year!!!)... let me make things complex to reflect a more realistic scenario.

How can I make it dynamic enough so that when I drag in the date dimension the measure will re-calculate dynamically?  For example, the users would like to display (depending on their needs) these counts on end-of-day, end-of-week, end-of-month, or end-of-quarter.  So if I drag the date dimension and filter the dimension, the measure will re-calculate based on whatever the filter is.  In addition, the user will use other measure to present:

  • No of Product Started
  • No of Product Still Active
  • No of Product Ended

These three measure would be presented "As of End of Day", "As of End of Week", "As of End of Month", "As of End of Quarter".  I have a date dimension that has all of these information... it's just a matter of using them in the filter.

Reporting "As of End-of-Month"

willson yuan on Mon, 01 Jan 2018 06:12:44

Hi JT59611,

Thanks for your response.

As you can see from below images, since this works fine at date level, it will work for other levels also :

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

JT59611 on Mon, 04 Jun 2018 18:47:30

Hello... sorry the suggested solution doesn't look like it's going to work.  I need a solution that will enable me to present the different measures side-by-side.  Also, different dimensions may be used to slice-and-dice the data.  Let me elaborate some detailed sample:

This below has a date dimension filtered with EndOfMonth only.  There will be a chance that the user will select different variation of the dimensions.  I'd need the measure to auto-calculate based on the dimension given.  Please help.  Thanks.

SSAS Tabular Model.  Measures: CountStarted (good), CountStillActive (help), CountEnded (good)

BTW, I'm after the measure: CountStillActive.  Basically, CountStillActive will measure the number of products that are still active as of that date dimension (row).