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
OR
Regular Visitor

Not Your Typical 12 Month Rolling Sum Problem

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!

 

 

 

 

5 REPLIES 5
v-cherch-msft
Employee
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@CheenuSing @v-cherch-msft

 

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.

Capture.JPG

 

 

 





Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@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.

 

Capture.JPG

 

 

 

Actually, my data is quite simple. Thanks in advance.

 

Capture.JPG

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




CheenuSing
Community Champion
Community Champion

Hi @OR,

 

You may check the link and see if it works for you.

 

http://community.powerbi.com/t5/Desktop/Show-last-13-months-based-on-user-single-slicer-selection/m-...

 

 

If it works accpet this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.