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

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