Forum Discussion
Calculate difference vs the previous period
Hello PBI Community.
I have a table with three columns: 1. Project Names 2. Forecast periods (1,2,3,4) and 3.Costs (€m). I need to create a visualization-table where I can see the differences between this period and the previous one for each project. This is what I've been trying so far but I cant make it work.
ryan_mayu Thanks for your contribution on this thread.
Hi martaoldi00 ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can update the formula of your measure [_MW Delta] as below:
_MW Delta = VAR Per = SELECTEDVALUE('Upper+Baseline'[Period]) VAR Prev_Period = Per - 1 VAR _project=SELECTEDVALUE('Upper+Baseline'[Project Names]) VAR Actual = CALCULATE( SUM('Upper+Baseline'[€m]), 'Upper+Baseline'[Period] = Per ) VAR Previous= CALCULATE( SUM('Upper+Baseline'[€m]), FILTER(ALLSELECTED('Upper+Baseline'),'Upper+Baseline'[Project Names]=_project&&'Upper+Baseline'[Period] =Prev_Period) ) RETURN IF(ISBLANK(Previous),BLANK(), Actual - Previous)Best Regards
5 Replies
ryan_mayu Thanks for your contribution on this thread.
Hi martaoldi00 ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can update the formula of your measure [_MW Delta] as below:
_MW Delta = VAR Per = SELECTEDVALUE('Upper+Baseline'[Period]) VAR Prev_Period = Per - 1 VAR _project=SELECTEDVALUE('Upper+Baseline'[Project Names]) VAR Actual = CALCULATE( SUM('Upper+Baseline'[€m]), 'Upper+Baseline'[Period] = Per ) VAR Previous= CALCULATE( SUM('Upper+Baseline'[€m]), FILTER(ALLSELECTED('Upper+Baseline'),'Upper+Baseline'[Project Names]=_project&&'Upper+Baseline'[Period] =Prev_Period) ) RETURN IF(ISBLANK(Previous),BLANK(), Actual - Previous)Best Regards
Anonymous Really amazing.
martaoldi00 I was just working on something very similar. I just want to present the way I choose to handle it.
I have used the attechment from above.
You can add a calculated column in the table to bring the previous values for each project.
Then you can create a measure to calculate the variance in percentage
Thank you so much Anonymous !! This worked perfectly
could you pls provide some sample data and expected output?
The table would be like this and the measure is the €Delta:
Project Period Units € Price Period Units € Price € Delta Project 1 8 21 21.7 9 21 21.7 0 Project 2 8 17 20.2 9 17 20.2 0 Project 3 8 28 28.3 9 28 28.3 0 Project 4 8 17 19.7 9 17 19.5 -0.2 Project 5 8 60 47.5 9 60 51 3.5 Project 6 8 14 18.5 9 14 13.6 -4.9 Project 7 8 18 21.9 9 21 20.5 -1.4
