DAX: DATEADD, DATESYTD not working correctly

[Aukje]

I am trying to accumulate values for the past year and using dateadd(dateytd) to do it.

However (after isolating the problem) my dateadd(dateytd) does not result in a calendar ending 2019-04-06, but in 2019-04-30 leading to faulty calculations.

I will add the formulae I used:

Table: YTD = DATESYTD('dimdate'[DateString];"30/6") results in a table from 190701-200406

Tables:
YTD LY (001) = DATEADD(DATESYTD('dimdate'[DateString];"30/6");-1;YEAR)

YTD LY (002) = DATESYTD(DATEADD('dimdate'[DateString];-1;YEAR);"30/6")

both result in a table from 180701-190430.

Does anyone recognize this problem and how to (in as simple manner if possible) limit YTD LY to 190406?

[AilleryO]

Hi @Aukje ,

First thing that strikes me, your date is named DateString, is it a real date value or a string (text) ?

You can use time Intelligence functions (DATEADD or YTD) only with dates (data type = Date or Date-Time).

Another thing, you seems to try to work with a date when you type "30/06",

which is not a date... which 30 of june do you want ? There is one each year as you noticed 😉

A date is day, month, and year (you must have the 3 parts).

This is not really solving your problem but might be a good start.

A Year to Date should look like this, I don't know what your 30/06 is standing for, filter ?

Sales YTD This Year = CALCULATE(TOTALYTD([Total Sales];MyCalendar[DateKey]))

Hope this help

[Aukje]

Hi!

Thank you for your input.

1) It is named DateString, but converted to datevalue, so that should not cause the problem.

2) "30/06" is the last date of the year (to have a year from july through end of june). In my understanding I should explicitly NOT submit a year in this scenario, the last day of the year, EACH year is 30/06. It is an optional setting to change the inherit datelogic in PowerBi from 0101-1231 to 0701-0630.

[AilleryO]

Hi,

For your fiscal year issue, the best is to add a column qith fiscal year in your Date Table, so makes things easier for your filters.

Have a look at :

https://community.powerbi.com/t5/Desktop/Creating-a-Fiscal-Year-amp-Fiscal-Quarter-in-a-DATE-calenda...

or

https://community.powerbi.com/t5/Desktop/Power-Query-Fiscal-Year-and-Quarter/td-p/383347

Should help you,

[Aukje]

Thank you for your reply! I do actually have a column for fiscal year. The fiscal year 2018-2019 should still only accumulate to 2019-04-06 (and tomorrow to 2019-04-07) etc, so not really sure how to achieve that even with having a column for fiscal year 🙂