Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good Afternoon Lovely Community,
I'm after some help with a brainscratcher I've got going on. My data looks like the below;
(End date is a calculated column, if Removed is Yes, used Date removed else use TODAY())
Name | Date Added | Date removed | Removed | Spine | End Date |
Dave | 12/03/23 | null | No | A | 14/03/23 |
Jim | 12/03/23 | null | No | B | 14/03/23 |
Barbara | 12/03/23 | 13/03/23 | Yes | A | 13/03/23 |
Bob | 13/03/23 | null | No | B | 14/03/23 |
Rita | 14/03/23 | null | No | B | 14/03/23 |
What I'd like is to create 3 running totals; one as a grand total and one for each spine, then plot all three on a line chart.
So
12/03/23 would show A = 2, B = 1, Total = 3.
13/03/23 would show A = 1, B = 2, Total = 3.
14/03/23 would show A = 1, B = 3, Total = 4.
Line chart would then show this, with date as x axis.
I'm completely stumped. Any help hugely appreciated.
@DAX_Defence See if this helps: Better Running Total - Microsoft Power BI Community
Thanks so much for the quick reply.
That works really well for the total, but I can't work out how to filter that to show each "Spine"?
@DAX_Defence It's just an extra filter clause, something like:
Better RT =
VAR __Date = MAX('Table'[Date])
VAR __Spine = MAX('Table'[Spine])
VAR __Table = FILTER(ALLSELECTED('Table'),[Spine] = __Spine && [Date] <= __Date)
RETURN
SUMX(__Table,[Value])
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |