Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AnttiKoskinen
Regular Visitor

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. 
 
Can you?
1 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] )
    )

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors