cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Avg % deviation of last 3 months with current month.

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

5 REPLIES 5
Anonymous
Not applicable

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

Resolver I

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
Not applicable

@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

Resolver I

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
Not applicable

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.