Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
@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.
Hi,
Share some data and show the expected result.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |