Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
57 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
40 | |
28 | |
22 |