cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Cumulative counts on line/bar graphs

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!

2 ACCEPTED SOLUTIONS
Community Champion

@Anonymous You will have do the calc at the year to have running total for each year. your measaure could be like below

Vol running total in Year =
CALCULATE(
SUM('Table'[Vol]),
FILTER(
ALLSELECTED('Table'[Date].[Year]),
ISONORAFTER('Table'[Date].[Year], MAX('Table'[Date].[Year]), DESC)
)
)

here is the expected output

Proud to be a Super User!

Community Champion

@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!

7 REPLIES 7
Community Champion

@Anonymous You will have do the calc at the year to have running total for each year. your measaure could be like below

Vol running total in Year =
CALCULATE(
SUM('Table'[Vol]),
FILTER(
ALLSELECTED('Table'[Date].[Year]),
ISONORAFTER('Table'[Date].[Year], MAX('Table'[Date].[Year]), DESC)
)
)

here is the expected output

Proud to be a Super User!

Anonymous
Not applicable

@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?

Community Champion

@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
Not applicable

That looks great - thank you!

Anonymous
Not applicable

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

Anonymous
Not applicable

@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!

Super User

@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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors