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.
Hi
Really hoping someone can help with this.
I have a dataset that is tracking a number of items that make up a project. Once every 6 months a report is submitted that shows the updated completion rate of each item in the project (the reports also contain other metrics but that aren't relevant here). I need to have a table visual that shows the metrics, including completion rate, for each item and need to show the most recent report data in the table visual.
To be able to always have the most recent data showing only I have used the following calculated column:
LatestPPR = IF('Local Supply_PPR_1'[MaxPPR] > 'Local Supply_PPR_1'[Date], 0, 1)
MaxPPR in the above is another calculated column that has the date of the most recent report so I can check it in LatestPPR against the date of that report. Then I just use this as a visual level filter. All of this works.
However, I also need to calculate the variance in the completion rate for each item between the current report and the previous report (ultimately I would like to be able to allow the user to select a range of reports to see the variance across them but that is for down the track).
To do that I have the following measure:
% of Completion Variance (from last PPR) =
var PrevValue = CALCULATE(SUM('Local Supply_PPR_1'[% of Completion (D)]), FILTER('Local Supply_PPR_1', 'Local Supply_PPR_1'[PPR# (Value only)] = MAX('Local Supply_PPR_1'[PPR# (Value only)]) - 1))
var CurrentValue = CALCULATE(SUM('Local Supply_PPR_1'[% of Completion (D)]), FILTER('Local Supply_PPR_1', 'Local Supply_PPR_1'[LatestPPR] = 1))
return
CurrentValue - PrevValue
If I use this in a table that has no visual filter then it works (ie. Item A has a completion rate of 30% in the current report, and a completion rate of 26% in the previous report so the above returns 4% which is correct). But if I place it in to a table that has the LatestPPR visual filter it doesn't work. For example, Item A shows a variance of 30% rate from the previous report.
I figure that with the visual filter it's filtering the data down so that there is essentially no previous report data present in the table so when it goes to calculate the variance it is calculating the current report completion rate minus nothing. That makes sense to me but I can't figure out how to resolve that. I've tried ALL and REMOVEFILTERS but to no avail. I've tried using all in the FILTER statement in both variables, I've tried using it outside of the FILTER statement in both variables but nothing is working.
Does anyone have any ideas here? I feel like I can get the theory (ie. I need to ignore the visual level filter on the PrevValue variable and then set a new filter context to get the previous report data) but I'm pushing at the limits of my knowlege of DAX here and can't work out how to formulate the measure to get what I need.
Any help would be greatly appreciated.
Thanks
@Anonymous , if you want two date ranges two work, refer to this example
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Based on independent date table. Sometime we can get thing work better with independent tables.
For standard period like month, quarter and week you can do MON, YOY and QOQ with single date table
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Hi Amit
Thanks for the reply. I've considered using time intelligence functions here but in this case they won't resolve the issue I'm having.
Thanks
@Anonymous , one case I shared of two date range. Hope that will also not work.
Can you please share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |