Forum Discussion
Solved
Dateadd when dynamic date filter
My scenario is this: I take end of each month a montly freeze of a set of data with last day of the month as the effective date and as a field Effective date to a table (fact_table). So thousands...
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.
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.