Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |