Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
need help with the formulae for calculating cumulative counts of items which falls in selected period of time between 2 dates.
Eg.
Name | StartDate | EndDate |
C1 | 10/1/2015 | 9/30/2016 |
C2 | 11/1/2015 | 10/31/2016 |
C3 | 10/1/2015 | 4/1/2017 |
In the above eg,. I want to show in a chart where the count of items will be shown as cumulative for a period.
Like, the count should be 3 from 10/1/2015 till 9/30/2016, next it will be 2 since the C1 expires by 9/30/2016. Next it will become 1 after 10/31/2016, For, C2 expires by that time. If its a line chart, then the line should continue with value 3 then down to 2 after 9/30/2016, then by 1 after 10/31/2016 ...meaning existence of that item for the selected period of time.
Actually the charts have the ability to show up the total counts of names for particular period that too taking in consideration of only 1 date value.
Can any one help me in this regard? - Thank you!
Let me know if the req,. is still not clear.
Solved! Go to Solution.
@Meerakhanna here's an alternative => for more see this link
Active Count = CALCULATE ( DISTINCTCOUNT ( Schedule[Name] ), FILTER ( Schedule, Schedule[StartDate] <= LASTDATE ( CalendarTable[Date] ) && Schedule[EndDate] >= FIRSTDATE ( CalendarTable[Date] ) ) )
Here you can see the results you get with both solutions (I've put them in a clustered column chart just for comparison purposes)
Items in Progress is not showing up in April 2017 in the picture - because you need to be on the DAY level !
Hope this also helps!
Hi @Meerakhanna
An 'events in progress' measure would be appropriate here.
Have a look at this paper (page 27 for the best performing DAX code):
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf
Adapting to your situation, you can use a measure like this:
Items in Progress = SUMX ( GENERATE ( SUMMARIZE ( Schedule, Schedule[StartDate], Schedule[EndDate], "Rows", COUNTROWS ( Schedule ) ), INTERSECT ( LASTDATE ( 'Calendar'[Date] ), DATESBETWEEN ( 'Calendar'[Date], Schedule[StartDate], Schedule[EndDate] ) ) ), [Rows] )
This measure assumes your table is called Schedule and you also have a 'Calendar' table, but no relationship between the two.
Also, the measure returns the Items in Progress on the last date of the current period (e.g. the last date of the month if you are browsing by month).
Owen 🙂
@Meerakhanna here's an alternative => for more see this link
Active Count = CALCULATE ( DISTINCTCOUNT ( Schedule[Name] ), FILTER ( Schedule, Schedule[StartDate] <= LASTDATE ( CalendarTable[Date] ) && Schedule[EndDate] >= FIRSTDATE ( CalendarTable[Date] ) ) )
Here you can see the results you get with both solutions (I've put them in a clustered column chart just for comparison purposes)
Items in Progress is not showing up in April 2017 in the picture - because you need to be on the DAY level !
Hope this also helps!
Thank you!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |