Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Varshi288
Resolver I
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.

 

Avg.PNG

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@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

 

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

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
 
 
2.PNG
Anonymous
Not applicable

@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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors