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 of rows which are linked to last day of the repective month.
I've created date table to my model using code:
dim Date Table =
VAR MinYear = YEAR ( MIN ('fact_table'[Effective date]) )
VAR MaxYear = YEAR ( MAX ('fact_table'[Effective date]) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Year", YEAR( [Date] ),
"Year-Month", FORMAT ([Date],"YYYYMM"),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Quarter", "Q" & TRUNC( (MONTH( ([Date] ) - 1 ) / 3 ) + 1 ),
"Is Last Effective date", IF(MAX('fact_table'[Effective date]) = [Date],1,0)
)
Date table has dates based on the fact_table effective date min and max and I've created Is Last Effective date boolean in date table to be able to pinpoint the latest effective date added to the fact_table.
I've created measure Reported value = SUM('fact_table'[value_field] and used dateadd to have 3MO, 6MO, 9MO and 12MO ago figures to my report.
Reported value 3MO Ago = CALCULATE([Reported value],DATEADD('dim Date Table'[Date],-3,MONTH)) etc.
And I've set reports page level filter to be Is Last Effective date = 1 (the last day of last month there's data in fact_table) which then makes my reports dynamic when new patch of data appears in fact_table.
Everything has been running smoothly until we came to 28.2.2023 data update and 3MO, 6MO and 9MO ago measures return blank. Year ago numbers appear normally so no problem with those and end of January (31.1.2023) I did not have any issues.
If I manually filter Year-Month as 202302 all measures work perfectly but using Is Last Effective date as page filter causes a problem which I can not figure out.
If I manually filter Year-Month as 202302 all measures work perfectly but using Is Last Effective date as page filter causes a problem which I can not figure out.
Can you?
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] ) )
5 Replies
What 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.
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] ) )
