Dateadd when dynamic date filter

[AnttiKoskinen]

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. 

Can you?

[johnt75]

What do you get if you go into the data view and filter the date table for Is Last Effective Date = 1 ?

[AnttiKoskinen]

28.2.2023 and related field values to it.

[johnt75 · Accepted solution]

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] )
    )

[AnttiKoskinen]

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.