Calculation of Difference between Running Total

[Alirezam]

Hi mates, 

I have two years of data (2018, 2019) as I attached. As we go along with 2019, I need to show the difference between two values:

Suppose that today is 4th July. The sum of 2018 (from the beginning to 4 July) need to be deducted from the sum of 2019 (from the beginning till today) which is 34-30=4

Do you know how to write this new measure?

This measure will be obviously updated every day.

Thanks a lot if you might have any clue. 

[MattAllington · Accepted solution]

You need a calendar table https://exceleratorbi.com.au/power-pivot-calendar-tables/

then you can write time intelligence. https://exceleratorbi.com.au/dax-time-intelligence-beginners/

Total ytd = TOTALYTD(sum(table[column]),calendar[date])

Total ytd LY = calculate(TOTALYTD(sum(table[column]),calendar[date]),sameperiodprioryear(calendar[date]))

[Alirezam]

Matt, thanks for your help. However, when I use Calendar[date] in the function, it returns the value for the whole year. How to define TODAY in these expressions so that it knows that I want the calculation till today.

Cheers

[Ashish_Mathur]

Hi,

If the Calendar Table has dates only till July 4 then the suggested formulas should work just fine.  It looks like the Dates in your Calendar Table are till December 31, 2019.

[MattAllington]

I call this "calendar over run".  If you calendar table this year has future dates, then the calculation last year will also have matching dates for the same period last year.  There are various ways to solve this problem.  I suggest you add a column in your calendar table indicating if each data is "future or not".  Something like this.

Future = IF('Calendar'[Date]>TODAY(),TRUE(),FALSE())

Once you have this column, you can add it as a page or visual filter and filter out future dates.  This should fix it.