Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |