Cumulative Sales By Year

DAX DAX Calculations
1

I’ve currently got a graph where I overlay this years sales compared to the same last years cumulatively, thanks to this video.

Comparing Actual vs Budget vs Last Year Results in Power BI w/DAX - YouTube

I’d like to improve on this by overlaying multiple years ontop of each other, with possibly a slicer for the user to select the years they want to view. I was hoping I could just use my normal cumulative measure:

 Cumulative Total Charges = 
 CALCULATE(
     [Total Sales],
     FILTER(
         ALL( Dates ),
         Dates[Date] <= MAX( Dates[Date] )
         )
     )

and then drop in the year as a legend on the graph and job done. However instead it seems to do 1 year, then the next year starts at the end of the previous:

image
image859×612 32.5 KB

I’m assuming I have to group or something to make the cumulative measure restart at the start of each year but can’t seem to get it right.

I’ve pulled a dummy dataset from eDNA since it’s just basic sales data I’m using.
DAX Formula Patterns - Dataset.xlsx (785.5 KB)

2

Hello @jamie.bryan,

Thank You for posting your query onto the Forum.

So in order to achieve the results based on the scenario that you’ve specified. We’ll write a simple measure for Year-to-Date where cumulative total will re-start every year. Below is the measure alongwith the screenhot of the final results provided for the reference -

Total Sales YTD = 
IF( ISBLANK( [Total Sales] ) , 
    BLANK() , 
    CALCULATE( [Total Sales] , 
        DATESYTD( Dates[Date] , "31/12" ) ) )

Final Results
Final Results1469×859 56 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Cumulative Sales By Year - Harsh.pbix (657.3 KB)

1 Like
3

Hi @Harsh,

That’s perfect, works a treat!

Thanks!

4

Hello @jamie.bryan,

You’re Welcome!! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thansk and Warm Regards,
Harsh