Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors