Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors