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
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 = COUNTROWS ( FILTER ( 'Fact', 'Fact'[Start Date] <= MAX ( CalDate[Date] ) && 'Fact'[End Date] >= MAX ( CalDate[Date] ) ) )
JT59611 on Fri, 29 Dec 2017 17:12:08
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
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 :
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 MSDNFSF@microsoft.com