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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.