Alternative to DATESINPERIOD for Rolling Average

[Anonymous]

hi all

Can someone suggest an alternative to the DATESINPERIOD function that allows me to go back and grab the last three months? Basically something to replace the below highlighted section.

My data is not on a day level and thus I can not use a date table and standard functions. I thus need a custom function.

[v-tangjie-msft · Accepted solution]

Hi @Anonymous ,

Please try this measure. 

Average Rolling 3 months2 =
VAR a =
    SUMMARIZE (
        TOPN (
            3,
            FILTER ( ALL ( DIM ), [YearMonth] <= MAX ( 'DIM'[YearMonth] ) ),
            [YearMonth], DESC
        ),
        [PeriodNoKey]
    )
RETURN
    IF (
        COUNTROWS ( a ) >= 3,
        CALCULATE ( SUM ( Facts[Amount] ) / 3, Facts[PeriodNoKey] IN a ),
        BLANK ()
    )

Then the result is as follows.

Best Regards,

Neeko Tang

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

[Anonymous]

Thank you very much @v-tangjie-msft 
I have some technical issues wrt my specific date/time table, but will try to sort that out on my own.

[jonathanbergen]

have a look at this article, in particular the WINDOW function, it might work for your needs.

edit: forgot link!

Introducing window functions in DAX - SQLBI

[Anonymous]

hi @jonathanbergen 
the WINDOW function does not seem to work in tabular model?

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=y7ucCjtADsg&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=13

[Anonymous]

hi @Padycosmos 

The WINDOW function does not seem to work in tabular model?

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[Anonymous]

hi @Ashish_Mathur 

Thank you for your response.

See below. I only have the amount values and also the reporting periods at month level. Thus it needs to shift in months and not days.

Facts:

DIM:

RESULT EXPECTED:

[Ashish_Mathur]

Hi,

Share the first 2 tables in a format that can be pasted in an MS Excel file.

[Anonymous]

hi @Ashish_Mathur 

please see the response 4 of @v-tangjie-msft  - there is a pbix file with the data

I am looking for the solution as a measure.

Thank you for responding!

[Ashish_Mathur]

I misinterpreted your question.  Sorry but i will not be able to help here.

[v-tangjie-msft]

Hi @Anonymous ,

Please try replacing it with the datesbetween() function:

DATESBETWEEN(Date[Date],
    DATEADD(LASTDATE(Date[Date]),-3,MONTH)
    ,LASTDATE(Date[Date])
)

 Please refer to the following document for more information.

Rolling Average 12 Months | Power BI Exchange (pbiusergroup.com)

DatesInPeriod vs DatesBetween; DAX Time Intelligence for Power BI - RADACAD

Rolling average last 3 months - Microsoft Power BI Community

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Anonymous]

hi @v-tangjie-msft 

I dont have days in the model. Amounts and periods at month level only.

[v-tangjie-msft]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Average Rolling 3 months = var a=SUMMARIZE(TOPN(3,FILTER(DIM,[YearMonth]<=EARLIER(DIM[YearMonth])),[YearMonth],DESC),[PeriodNoKey])
return IF(COUNTROWS(a)>=3,CALCULATE(SUM(Facts[Amount])/3,Facts[PeriodNoKey] in a),BLANK())

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Anonymous]

hi @v-tangjie-msft 

Many thanks for this solution.

How do we convert this to a measure? I fear this calculated column might be costly in my very large fact table..