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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.