Changing Values to 0 after a specified date in Dax Table

[GarlonYau]

Hi There! I've been stuck on this problem for a while, so any help is appreciated!

I've got a DAX measure that calculates a rolling 3 month average around Absenteeism numbers.

Here's the formula I've been using and it's working so far, when I show it in a table.

Moving 3 Months Average = 
VAR temp =
    CALCULATE (
        AVERAGEX (
                SUMMARIZE (
                'Summarized Time Off Request',
                'DateTable'[Date].[Year],
                'DateTable'[Date].[Month],
                "sumtotal", [Absenteeism (All) %]
            ),
            [sumtotal]
        ),
        DATESINPERIOD (
            'DateTable'[Date],
            LASTDATE ( 'DateTable'[Date] ),
            -3,
            MONTH
        )
    )
RETURN
   temp

However, I can't seem to figure out what I need to change to the formula to make any returned value AFTER Dec 31, 2021 to = 0 (would be the part indicated in red).
Reason for this is because I'm merging an old data set with hardcoded monthly numbers and a new data set coming from a system.

Thanks for you help in advance!

[GarlonYau]

@amitchandak  Thanks for your reply!

Your advice worked somewhat. However, any month with no data now yields the value 6.52% for some odd reason.

Here's the measure.

Moving 3 Months Average = 
VAR temp =
    CALCULATE (
        AVERAGEX (
                SUMMARIZE (
                'Summarized Time Off Request',
                'DateTable'[Date].[Year],
                'DateTable'[Date].[Month],
                "sumtotal", [Absenteeism (All) % Historical]
            ),
            [sumtotal]
        ),
        DATESINPERIOD (
            'DateTable'[Date],
            LASTDATE ( 'DateTable'[Date] ),
            -3,
            MONTH
        )
    )
RETURN
   if(max('DateTable'[Date]) >= Date(2022,01,01) , 0 , temp)

And here's the measure for [Absenteeism (All) % Historical]

Absenteeism (All) % Historical = CALCULATE(AVERAGE('Summarized Time Off Request'[Value]),'Summarized Time Off Request'[Metric]="Absenteeism (All)")

[v-rongtiep-msft · Accepted solution]

Hi @GarlonYau ,

Please have a try.

Modify your measure.

Moving 3 Months Average = 
VAR temp =
    CALCULATE (
        AVERAGEX (
                SUMMARIZE (
                'Summarized Time Off Request',
                'DateTable'[Date].[Year],
                'DateTable'[Date].[Month],
                "sumtotal", [Absenteeism (All) % Historical]
            ),
            [sumtotal]
        ),
        DATESINPERIOD (
            'DateTable'[Date],
            LASTDATE ( 'DateTable'[Date] ),
            -3,
            MONTH
        )
    )
RETURN
If(max('datetable'[date]<date(2022,1,1),tmep,0)

If it still doesnot help, please provide your pbix file without privacy information and desired output with more details. (Or some sample data).

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@GarlonYau , You can add code in return

if(max('DateTable'[Date]) >= Date(2022,01,01) , 0 , temp)