Cumulative counts on line/bar graphs

[HawkB]

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! 

[negi007 · Accepted solution]

@HawkB 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 · Accepted solution]

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

[HawkB]

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

[negi007 · Accepted solution]

@HawkB i am sharing updated solution, it will work on months and year both. have taken inspiration from below

Computing running totals in DAX - SQLBI

[HawkB]

That looks great - thank you! 

[HawkB]

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 

[HawkB]

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

@HawkB 

Cumulative = CALCULATE (
    COUNT ( 'Team'[DateRegistered]),  
        'Team'[DateRegistered] <= MAX ( 'Team'[DateRegistered])
)

@HawkB if it's not work please share your sample data and sample output in table format? Or a sample pbix after removing sensitive data.