Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi! I hit a wall and need your help. Here is the situation:
I have a sales dataset for several years: 2016 - 2020, with data reported in monthly buckets.
I need to achieve the following: for year 2018 display (in a table and in a chart, filtered to year 2018) on a monthly basis the trailing (rolling) 12 month sum of sales. This means that for January 2018 - I would display the sum of sales from February 2017 through January 2018. For February 2018 - I would display the sum of sales from March 2017 through February 2018, and so on.
When applying the typical:
Calculate ( sum[sales], datesinperiod(...)) approach, the following happens:
For January 2018, I get not the sales from February 2017 through January 2018, but just the sales in the month of January, 2018. For February 2018, I get not the sales from March 2017 through February 2018, but just the sales in the month of January and February of 2018, and so on. Finaly by December 2018, I get a proper 12 month rolling sum of sales, ranging January 2018 to December 2018.
What I need to accomplish is, despite filtering the visual (table, or chart) for 2018, I need the 12 month rolling sum measure to look back, outside the bounds of the filtered (for 2018) chart or table, and retrieve the appropriate 12 months, and produce their sum.
Any help in achieving this would be greatly appreciated. Please help!
Hi @OR
Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.
Reagrds,
Cherie
I am also interested in this subject because this is also useful in my job and can reduct the DAX I wrote.
Measure = VAR LDATE=MAX('Sheet1'[Month]) var FDATE = EDATE(LDATE,-3) RETURN CALCULATE(SUM(Sheet1[sales]),DATESBETWEEN('Sheet1'[Month],FDATE,LDATE))
Above is the coding I used by reading the subject that CheenuSing recommended. That is very helpful.
Here is my question. If my data starts on 2018/1/1, then for Jan, Feb and Mar, we don't have completed last four month's value.
So is it possible to modify the coding to make the results only show from Apr 2018?
Thanks in advance.
Proud to be a Super User!
Hi @ryan_mayu,
I am assuming
a) the data always starts from 01/01/2018 - Jan 2018.
b) Beyond Apr 2018 how the sum of previous three months - as you already doing.
By this logic going forward - May 2018 will show sum of Feb to Apr 2018,.., Jan 2019 will show sum of Oct to Dec 2018.
I think the following tweak to the measure should work
Measure =
VAR FirstMonth = Min('Sheet1'[Month] ) / * get the first month of the data set */
VAR LDATE=MAX('Sheet1'[Month])
var FDATE = EDATE(LDATE,-3)
VAR Is3MonthsComplete = LDATE - FirstMonth /* This gives the difference between First and Latest Month in the dataset */
RETURN
If (Is3MonthsComplete > 3 ,
CALCULATE(SUM(Sheet1[sales]),DATESBETWEEN('Sheet1'[Month],FDATE,LDATE))
, Blank())
/* If the difference is >3 months then calculate the sum of last 3 months, else show as blank */
Let me know if this works for you.
Cheers
CheenuSing
Thanks for your help. I used the same coding I wrote last time. However, it doesnot work now. Do you know the reason?
What's more, the date filter also doesnot affect the table.
Actually, my data is quite simple. Thanks in advance.
Proud to be a Super User!
Hi @OR,
You may check the link and see if it works for you.
If it works accpet this as a solution and also give KUDOS.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |