Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgericke
New Member

Creating a 3 month moving total with date slicer

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.

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @mgericke 

 

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

Table:

d1.PNG

 

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:

d2.PNG

 

Best Regards

Allan

 

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

 

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @mgericke 

 

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

Table:

d1.PNG

 

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:

d2.PNG

 

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
Super User
Super User

@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
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.