Forum Discussion
Dateadd when dynamic date filter
The problem is that the DATEADD is returnin 28 November, which has no data. Try
Reported value 3MO Ago = VAR CurrentDate = LOOKUPVALUE ( 'Date'[Date], 'Date'[Is Last Effective Date], 1 ) RETURN CALCULATE ( [Reported value], TREATAS ( { EOMONTH ( CurrentDate, -3 ) }, 'Date'[Date] ) )
Super UserWhat do you get if you go into the data view and filter the date table for Is Last Effective Date = 1 ?
28.2.2023 and related field values to it.
- johnt75
The problem is that the DATEADD is returnin 28 November, which has no data. Try
Reported value 3MO Ago = VAR CurrentDate = LOOKUPVALUE ( 'Date'[Date], 'Date'[Is Last Effective Date], 1 ) RETURN CALCULATE ( [Reported value], TREATAS ( { EOMONTH ( CurrentDate, -3 ) }, 'Date'[Date] ) )This solves my problem now but I will face it again in the future when number of days is not 28.
In my case best solution would probably be that instead Last Effective date = 1 returns 28.2.2023 it should return 1 to all dates in this last month (and always 1 for all days in the last reporting month). I have values only for one day for each month thus I can select all the days without numbers changing.
- johnt75
The code I posted should work for any month, it will get the last day of the month 3 months ago regardless of the number of days in any month.