Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there
I need to calculate number of active cases at any date, and show them in a line chart
Have a fact table with around 2,500,000 records/cases, dates mostly opening days monday - friday, but want to show for all days
Every case has a start date and a end date (if the case is closed)
Any sugestions?
Regards Claus
Solved! Go to Solution.
Hi @Claus_Vad,
You need a calendar table firstly, and you could create it by a Calculated table.
Calendar =
VAR minDate =
MIN ( 'Fact Table'[start date] )
VAR maxDate =
TODAY ()
RETURN
CALENDAR ( minDate, maxDate )
Then, you need a Measure to count cases.
Active Case Number =
VAR num =
CALCULATE (
COUNT ( 'Fact Table'[case] ),
FILTER (
'Fact Table',
'Fact Table'[start date] <= MAX ( 'Calendar'[Date] )
&& ISBLANK ( 'Fact Table'[end date] )
)
)
RETURN
IF ( ISBLANK ( num ), 0, num )
The result looks like this.
Also, attached the pbix file.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Thanks for your sugestion
It was almost right, this will do what I want
Hi @Claus_Vad,
You need a calendar table firstly, and you could create it by a Calculated table.
Calendar =
VAR minDate =
MIN ( 'Fact Table'[start date] )
VAR maxDate =
TODAY ()
RETURN
CALENDAR ( minDate, maxDate )
Then, you need a Measure to count cases.
Active Case Number =
VAR num =
CALCULATE (
COUNT ( 'Fact Table'[case] ),
FILTER (
'Fact Table',
'Fact Table'[start date] <= MAX ( 'Calendar'[Date] )
&& ISBLANK ( 'Fact Table'[end date] )
)
)
RETURN
IF ( ISBLANK ( num ), 0, num )
The result looks like this.
Also, attached the pbix file.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Thanks for your sugestion
It was almost right, this will do what I want
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |