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!
You may check the link and see if it works for you.
If it works accpet this as a solution and also give KUDOS.
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.
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.
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
VAR FirstMonth = Min('Sheet1'[Month] ) / * get the first month of the data set */
var FDATE = EDATE(LDATE,-3)
VAR Is3MonthsComplete = LDATE - FirstMonth /* This gives the difference between First and Latest Month in the dataset */
If (Is3MonthsComplete > 3 ,
/* 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.
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.