Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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

202034
20210
20220
  

 

Would still be reported in the graph as 34, 34, 34.

 

Thanks! 

2 ACCEPTED SOLUTIONS
negi007
Community Champion
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
negi007_0-1674044762688.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

@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

 

negi007_0-1674136753946.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

7 REPLIES 7
negi007
Community Champion
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
negi007_0-1674044762688.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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? 

@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

 

negi007_0-1674136753946.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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! 

 

 

Demo 

Mahesh0016
Super User
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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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