Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I have a measure which I use to count cumulative registrations - it's simple and it has mostly worked:
Cumulative = CALCULATE (
COUNT ( 'Team'[DateRegistered]),
FILTER (
ALLSELECTED ( 'Team' ),
'Team'[DateRegistered] <= MAX ( 'Team'[DateRegistered])
)
)
However, it struggles if there are no registrations in a given year - so if there were 30 in 2021, but 0 in 2022 it won't carry over the figure from 2021 but instead will just not report on it.
This results in either a line graph where the line terminates early, or a bar graph where the relevant block (if I'm using a legend to split registrations by team) will just be omitted.
I understand that my formula is reporting exactly how it should be, but how would I make it report last years figure if there was nothing else for the year?
E.g.
2020 | 34 |
2021 | 0 |
2022 | 0 |
Would still be reported in the graph as 34, 34, 34.
Thanks!
Solved! Go to Solution.
@Anonymous You will have do the calc at the year to have running total for each year. your measaure could be like below
Proud to be a Super User!
@Anonymous i am sharing updated solution, it will work on months and year both. have taken inspiration from below
Computing running totals in DAX - SQLBI
Proud to be a Super User!
@Anonymous You will have do the calc at the year to have running total for each year. your measaure could be like below
Proud to be a Super User!
@negi007 Thank you again for this solution, it works perfectly on years - but when I need to use months and years (as if I just use month the count doesn't work) I am struggling with what changes I need to make? Is it an adjustment to this code, or an entirely new approach that is required?
@Anonymous i am sharing updated solution, it will work on months and year both. have taken inspiration from below
Computing running totals in DAX - SQLBI
Proud to be a Super User!
That looks great - thank you!
Thank you - I will give this a try, my only concern would be the need to adapt for monthly as opposed to yearly totals (although I do appreciate that this is something that I'd not mentioned!). The code I gave at the start works for the majority of the things I need, it just doesn't work when a there's no registrations in a particular year.
EDIT: Just tried it and it works pefectly on the year front, exactly what I needed it to do. I'll have to deal with months within a year next, would that be a case of just adding [month] in?
Thanks again
@negi007 Sorry to bring this back again, I feel like I am almost there but the final steps are eluding me.
I've attached some basic detail and a demo, I don't need to calculate values as such, so switched SUM for COUNT - but as you can see on the tabs in the below:
Annual - this works perfectly.
Monthly - this works well, but I would probably remove the gaps by switching to categorical (although not ideal when it comes to setting a date range on a larger data set).
Monthy - Team - here the DAX stops working as it has done previously (continuing to display the value even if nothing else has changed).
EDIT - Duplicate of Annual - ignore this! Hopefully the above three tabs are enough of a demo, I had thought that this would be relatively straightforward, but have struggled with it!
@Anonymous
Cumulative = CALCULATE (
COUNT ( 'Team'[DateRegistered]),
'Team'[DateRegistered] <= MAX ( 'Team'[DateRegistered])
)
@Anonymous if it's not work please share your sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
125 | |
108 | |
60 | |
55 |