Rolling 5 Month AVG DAX Returns Monthly Total Instead - Why?

[Anonymous]

Hi everyone,

I am stumped! I can't figure out why the two formulas below return the same result. Am I missing somethng really obvious?

ExD Month = CALCULATE(TOTALMTD(SUM('Ex-Distributor'[ExDistributor Sales]),'DateDim'[Date]))

Ex-UD AVG (Month) = CALCULATE(SUM('Ex-Distributor'[ExDistributor Sales]),DATESINPERIOD('Ex-Distributor'[Month],LASTDATE('Ex-Distributor'[Month]),-5,MONTH))/CALCULATE(DISTINCTCOUNT('Ex-Distributor'[Month]),DATESINPERIOD('Ex-Distributor'[Month],LASTDATE('Ex-Distributor'[Month]),-5,MONTH))

Thanks a lot!

[amitchandak · Accepted solution]

@Anonymous , try somthing like this with date table

Rolling 5 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-5,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-5,MONTH), not(isblank((Sales[Sales])))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Did you have a date table , you should use date column instead of month column in formula:

Ex-UD AVG (Month) =
CALCULATE (
    SUM ( 'Ex-Distributor'[ExDistributor Sales] ),
    DATESINPERIOD (
        'DimDate'[Date],
        LASTDATE ( 'DimDate'[Date] ),
        -5,
        MONTH
    )
)
    / CALCULATE (
        DISTINCTCOUNT ( 'DimDate'[Date] ),
        DATESINPERIOD (
            'DimDate'[Date],
            LASTDATE ( 'DimDate'[Date] ),
            -5,
            MONTH
        )
    )

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

Best Regards,

Dedmon Dai

[amitchandak · Accepted solution]

@Anonymous , try somthing like this with date table

Rolling 5 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-5,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-5,MONTH), not(isblank((Sales[Sales])))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.