Compare Cumulative totals sameperiodlastyear

Symeon

New Member
Joined
Sep 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello everybody, I would like to ask a question about the comparison cumulative totals. I have a problem with some of my products which came later in my company. For example, master blocks came in May 2019 and I want to compare cumulative totals from May 2020 to May 2019. My problem is that Dax calculates the cumulative from Jan 2020 rather than May 2020 and I have the wrong result. I would be grateful if someone can help me!

My code Cumulative Yearly Total:=IF([TotalSales]>0;CALCULATE([TotalSales];FILTER(ALL(dDate);dDate[Date]<=MAX(dDate[Date]));VALUES(dDate[Year])))

YTDLY:=IF(HASONEVALUE(dDate[Month]);SUMX(VALUES(dDate[Month]);IF([TotalSales]>0;CALCULATE([Cumulative Yearly Total];SAMEPERIODLASTYEAR(dDate[Date])))))

comparisoncumulative:=IF(AND(HASONEVALUE(dDate[Year]);([YTDLY]>0));[Cumulative Yearly Total])

ChangeYTD:=IF([YTDLY]>0;[comparisoncumulative]-[YTDLY])
Thanks in advance!
 

Attachments

  • Cumulative Total.JPG
    Cumulative Total.JPG
    115 KB · Views: 8

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Symeon

New Member
Joined
Sep 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
comparison cumulative totals!
 

Attachments

  • Comparison Cumulative Totals.jpg
    Comparison Cumulative Totals.jpg
    206.1 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,108,741
Messages
5,524,560
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top