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
