cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
)
)```

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors