YTD Last year Dax

[Aukje]

Hello!

I am trying to accomplish a dashboard that continuously compares to last years accumulated results (YTD, year counts from YEAR-07-01 to YEAR(+1)-06-30).

For this I am calculating YTD for last year by using DATEADD, -1 YEAR.

However in this case it accumulates all dates in april for last year, and not just until YTD (2020-04-06) for 2019.

This is the formula I use:

= CALCULATE(SUM(case[Cost]);DATEADD(DATESYTD('DimDate'[DateString];"30/6");-1;YEAR))

I really do not understand why it accumulates all dates for April when I use this formula, any help would be greatly appreciated!

[amitchandak]

@Aukje 

YTD will accumulate data. So either use

CALCULATE(SUM(case[Cost]);DATEADD('DimDate'[DateString];-1;YEAR))

or use datesytd outside

CALCULATE(SUM(case[Cost]);DATESYTD(DATEADD('DimDate'[DateString];-1;YEAR);"30/6"))

[Aukje]

Hi,

Thank you, unfortunately neither solution worked (at the best generated the current outcome)

I have tried just creating a datetable to try and problem solve and:

date= DATESYTD('DimDatum'[DateString];"30/6") generates a table from 20190701-20200406

date = DATEADD(DATESYTD('DimDatum'[DateString];"30/6");-1;YEAR) generates a table from 20180701-20190430

Obviously my problem is somewhere within the DATEADD logic, however is has such a basic logic I cannot understand where it goes wrong.

[Aukje]

I display below examples showing the problem with the datesytd / dateadd function for clarification:

Showing effect of DATEADD on calendar

Showing date YTD is 190701-200406

[v-easonf-msft]

Hi , @Aukje 

Maybe your can refer  to this post to  filter  out you unwanted  days .

https://community.powerbi.com/t5/Desktop/Problem-with-LY-MTD-Calculations/td-p/993184

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Generally you use SAMEPERIODLASTYEAR for these kinds of things. 

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Aukje]

In my understanding DateAdd shoudl be more versatile than sameperiodlastyear (which is limited to going one year back), in addition to being more useful when my "year" is broken up from xx0701-x10630.

Still looking for a reason why my function does not work, as I prefer getting a more in depth understanding and not only solving the current problem by a work around 🙂