Calculating Total Average of last 3 months not including current month without using slicer

[israabuhasna]

I have searched everywhere on this forum and others to look for an answer but I have not been able to figure it out. I want to have a measure that calculates the total average of the last 3 months not including this month. And another measure that calculates the average of last three months on this date. So I can use a card measure to calc = last3MonthAvg / TotalLast3monthAvg and get a %.

Eg. in excel it is very simple:

But I need the '%PerDay' as a measure so I can multiply it by CurrentDates sales. I have managed to do the below in pivot table but have not been able to hold a measure with a single value such as the Avg. All help is appreciated!

[Syndicate_Admin]

Use the following formula for 3 Month, use BLANK () so that I would not get sales of the current month blank

Where Mes1 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-1; My ))

Where Mes2 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-2; My ))

Where Mes3 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-3; My ))

Where Mes4 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-4; My ))

Where Mes5 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-5; My ))

Where Mes6 = CALCULATE ([Net Sales ($)]; PARALLEL PERIOD (Calendar[Date];-6; My ))

Where M1 = Yes ( Mes1 <=0;0;1)

Where M2 = Yes ( Mes2 <=0;0;1)

Where M3 = Yes ( Mes3 <=0;0;1)

Where M4 = Yes ( Mes4 <=0;0;1)

Where M5 = Yes ( Mes5 <=0;0;1)

Where M6 = Yes ( Mes6 <=0;0;1)

return

Yes ([Net Sales ($)]= BLANK (); BLANK (); DIVIDE ( Mes1 + Mes2 + Mes3 + Mes4 + Mes5 + Mes6 ; M1 + M2 + M3 + M4 + M5 + M6 ))

[v-chuncz-msft · Accepted solution]

@israabuhasna 

You may take a look at the following posts.

• https://community.powerbi.com/t5/Desktop/I-want-to-get-an-average-of-three-months-from-next-month-to...
• https://community.powerbi.com/t5/Desktop/Cumulative-Sum-of-a-Measure-no-dates/m-p/800187#M385063

[amitchandak]

@israabuhasna You can use a rolling formula with a date dimension .

Example

Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Day))  

[Mariusz]

Hi @israabuhasna 

You can use DATESINPERIOD function, please see the below.

https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[israabuhasna]

Thank you for responding. It did not work. It will not give me a single value.