3 mos avg vs previous 3 months avg

[nmck86]

I am able to calculate my rolling 3 months

Rolling 3 Mo =
CALCULATE (
    TBL[VALUE],
   DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE( PREVIOUSMONTH( 'Calendar'[Date]) ),
        -3,
        MONTH))

However, now I want to be able to look at the previous 3 months less current month... so my current calculation is looking at august, july, and june... now I want a calculation that will look at july, june, and may... any help on this you guys can provide?

[v-jiascu-msft]

Hi @nmck86,

If you just choose the months manually. That could be easily done by changing the blue part. If you have a slicer, you also can change the blue to achieve your goal.

Rolling 3 Mo =
CALCULATE (
    TBL[VALUE],
   DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE( PREVIOUSMONTH( 'Calendar'[Date]) ),
        -3,
        MONTH))

If you have more questions, please provide a sample.

Best Regards,

Dale

[nmck86]

Sample data below... I am using the above formula which is the same as @v-jiascu-msft @Greg_Deckler

What I would like is that for the month of April, I am using a filter for that current month and want the rolling avg which would be 46.7 (april, march, feb) and right beside of that I want to have the 3 months previous to show up which would be 43.3... any thoughts on how to accomplish this?

Jan

50 

Feb

45

March

35

April 

60

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(AVERAGE(TBL[VALUE])),DATESBETWEEN(Calendar,EDATE(MIN(Calendar[Date]),-3),MIN(Calendar[Date])-1))

Ensure you have a calendar Table with Months in that table via this calculated column formula Month=FORMAT(Calendar[Date],"MMMM").  In your visual, drag Month from the Calendar Table.  There should be a relationship from the Date column of the TBL table to the Date column of the Calendar Table.

[nmck86]

One other issue is the fact that the 3 month average is for a measure in my table... I am trying the below but how do I get it do a 3 month rolling average? 

@Ashish_Mathur 

@v-jiascu-msft

@Greg_Deckler

Average = AVERAGEX(VALUES(Table1[Date].[Month]),[Total])

[nmck86]

One other issue is the fact that the 3 month average is for a measure in my table... I am trying the below but how do I get it do a 3 month rolling average? @Ashish_Mathur@v-jiascu-msft@Greg_Deckler

Average = AVERAGEX(VALUES(Table1[Date].[Month]),[Total])

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(AVERAGEX(VALUES(Calendar[Month]),[Total]),DATESBETWEEN(Calendar,EDATE(MIN(Calendar[Date]),-3),MIN(Calendar[Date])-1))

If this measure does not work, then share the link from where i can download your PBI file and show your expected result there.

[nmck86]

Hello! Attached is the PBIX FILE. Unfortunately, the formula you suggested does not work.

https://drive.google.com/file/d/1zriYzkycfYx-zvvdyEedCEbbJ_5endIN/view?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  For August, rolling 3 months average would be from May to July.

[nmck86]

what does the abcd imply in the calculation out of curiousity? and so that I can articulate what it is actually doing?

[Ashish_Mathur]

Hi,

ABCD is just the title of the new column which has entries for the meausre that you have already computed.  Is my formula returning the correct result?

[nmck86]

It is returning the results for May-July but I will also need June-August to show as well. The thought is to show current 3mo avg vs previous 3mo avg.

[Ashish_Mathur]

Check my most recent post with the revised DATESBETWEEN() function.

[nmck86]

Thanks a ton! I think I was typing too fast. I appreciate the help. 

[Ashish_Mathur]

You are welcome.

[nmck86]

@Ashish_Mathur can I use this calculation for current month as well? So as folks select a particular question it shows current month then if they select several months it shows the number as an average?

[Ashish_Mathur]

Hi, I do not understand your question. Share some data and show the expected result.

[Ashish_Mathur]

Hi,

I do not understand your question.  Share some data and show the expected result.

[nmck86]

Sure, below is the link to the pbix file that I need assistance with. I want to use a formula that will allow for the total score formula to show the avg if you select several months. So basically in the example below you will see that I have 2 months selected and for July and August and I would expect for CALL FAILURE to see 41.56 as the avg of those 2 months combined but the system aggreagtes the data and gives me an average that is based on the incidents rates for each respective month. 

https://drive.google.com/file/d/1uQ2B43axGYoh1_FahMefoBlvV_Ew7ebG/view?usp=sharing

[nmck86]

I just saw your post and it seems to capture May-July. If I remove the -1 will that give me June-Aug?

[Ashish_Mathur · Accepted solution]

Hi,

to get July to August, replace the DATESBETWEEN() function with this

DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-2),MAX(Calendar[Date])))