Creating a 3 month moving total with date slicer

[mgericke]

I'm looking to create a 3 month moving total measure.  I've got sales data by invoice/line, all lines have a date.  I want to show a 3 month moving total, for example in my date slicer if I were to select July 2019 to June 2020 in my slicer, I would want to see a data point once per month for that period that shows a 3 month moving total.  For example, for June 2020 it would give me the total invoice sales for June, May, & April summed but as my June 2020 data point.  For May 2020 it would sum May, April, March, etc.

[v-alq-msft · Accepted solution]

Hi, @mgericke 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a relationship between two tables. You may create a calculated table and a measure as below.

Calculated column:

YearMonth = VALUE(FORMAT('Calendar'[Date],"yyyymm"))

Measure:

Three months moving total = 
IF(
    SELECTEDVALUE('Calendar'[YearMonth]) in ALLSELECTED('Calendar'[YearMonth]),
    CALCULATE(
        SUM('Table'[Sales]),
        DATESINPERIOD(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            -3,MONTH
        )
    )
)

Result:

Best Regards

Allan

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

[amitchandak]

@mgericke , Try like date calendar

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))  

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[mahoneypat]

Assuming you have a Date table in your model (marked as a Date table) with a relationship to your sales table, you could use a measure like this one

Rolling 3 Month Total =
VAR __maxdate =
    MAX ( 'Date'[Date] )
VAR __mindate =
    EDATE ( MIN ( 'Date'[Date] ), -2 )
RETURN
    CALCULATE (
        SUM ( Sales[Amount] ),
        ALL ( 'Date' ),
        DATESBETWEEN ( 'Date', __mindate, __maxdate )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat