filter visual dates over the date splicer for last three months

[DuskyLeaf]

I am looking to show total hours for the last three months for a specific department.

I have matrix visual that totals hrs over the splicer date range.

I have hrs values that I need to display on the visual outside the splicer dates.

I have created a Auto Calendar date table (make as date table) and set up a relationship between the Calendar Date table to a start date on my primary hrs value table. Both date columns are set to DATE:dd/mm/yyyy

Using the measure below does not filter the values for the previous month.

1mths =

CALCULATE(

    SUM('msnfp_participationschedule'[Time Worked]),

    DATEADD ( 'sCALENDAR'[Date], -1, MONTH )

)

The formula for a standard table is not filtering by the date correctly. The totals should be 15 and 20hrs

[Anonymous · Accepted solution]

Hi @DuskyLeaf,

It seems like you are working with direct query connection mode tables, I'd like to suggest you use date function to manually define the filter range instead of use time intelligence functions: (time intelligence functions are based on the hidden calendar table, direct query connection mode will affect it)

previous Month =
VAR currDate =
    MAX ( 'sCALENDAR'[Date] )
RETURN
    CALCULATE (
        SUM ( 'msnfp_participationschedule'[Time Worked] ),
        FILTER (
            ALLSELECTED ( 'msnfp_participationschedule' ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
                && [Date] <= currDate
        )
    )

Regards,
Xiaoxin Sheng

[DuskyLeaf]

... the tables are DirectQuery except sCalendar Table which is import