Avg % deviation of last 3 months with current month.

[Varshi288]

I have requirement where I need to find Avg DC of last 3 months and compare with current month DC for a particular product code .

With the given data I need to find avg DC of last 3month Avg DC.(i.e Dec-Jan& Feb) and compare with March month with %.

"last month DC for a particular product code in a particular PU deviate more than x % from avg of 3 previous months"

PLeasse help.

[Anonymous]

@Varshi288 -

You can do something like this to alter the Filter context, effectively changing the time period you're calculating:

Avg DC last 3 months = 
CALCULATE(
   [Avg DC],
   DATESBETWEEN(
        'Date'[Date],
        MINX(DATEADD('Date'[Date],-3,MONTH),[Date]), //Start of 3 months ago
        MAXX(DATEADD('Date'[Date],-1,MONTH),[Date]) //End of last month
    )
)

My preference is to have a robust date table, which allows this simpler, faster calculation:

Avg DC last 3 months = 
var currentmonth = MAX('Date'[Relative Month])
return CALCULATE(
   [Avg DC],
   'Date'[Relative Month] >= currentmonth - 3,
   'Date'[Relative Month] < currentmonth
)

Cheers!

Nathan

[Varshi288]

Thank you Nathan for you response. but this is not filtering the date showing " cant dispaly the visul"

I want to Find the last 3 months avg of Avg DC and compare it with persent month Avg DC.

[Anonymous]

@Varshi288 - Could you please share a sample pbix, without sensitive data, or share screenshots of the formulas you used and the visual options and results?

Thanks,

Nathan

[Varshi288]

Hi  I am using below formulae. however percentage Deviation  same for all which is wrong.

Avg DC last 3 months =

CALCULATE(

[Avg DC],DATESINPERIOD(Period[Date],LASTDATE(Period[Date]),-3,MONTH))/3

[Anonymous · Accepted solution]

@Varshi288  -

For the 3 month measure, don't divide by 3 at the end.

For the Avg Deviation, it's -2/3 because you divided by 3. Try different numbers and more decimal places.