Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
98 | |
97 | |
38 | |
38 |
User | Count |
---|---|
153 | |
122 | |
76 | |
73 | |
66 |