Forum Discussion
Variances between columns
Trying to replicate something that is easy in excel. Each month the business I work for do a 12 month revenue forecast and we compare forecasts. My stakholders want the ability to select any 2 forecasts and for the table to give varainces between the 2 selected forecasts.
So in the example below the forecast submitted in July 2017 and submitted in November 2017 have been selected. I have this working in Power BI, however the how do I create a dynamic measure that gives the variance based on what was selected?
| Submission Month | ||||
| Jul-17 | Nov-17 | |||
| Revenue | Revenue | Variance | ||
| Forecast Month | Aug-17 | $ 977,976 | ||
| Sep-17 | $ 993,591 | |||
| Oct-17 | $ 979,292 | |||
| Nov-17 | $ 1,057,841 | |||
| Dec-17 | $ 981,192 | $ 979,631 | -$ 1,561 | |
| Jan-18 | $ 990,874 | $ 993,006 | $ 2,132 | |
| Feb-18 | $ 1,016,939 | $ 1,004,626 | -$ 12,313 | |
| Mar-18 | $ 1,020,473 | $ 1,015,008 | -$ 5,465 | |
| Apr-18 | $ 1,167,460 | $ 1,135,328 | -$ 32,132 | |
| May-18 | $ 982,489 | $ 977,033 | -$ 5,456 | |
| Jun-18 | $ 983,388 | $ 951,256 | -$ 32,132 | |
| Jul-18 | $ 1,009,297 | $ 1,009,132 | -$ 165 |
19 Replies
Worth a 2026 update to this thread. The LASTDATE/ALLSELECTED pattern in the accepted solution works, but requires significant setup and as you experienced, typos in DAX functions break the whole thing silently.
For this exact use case end-users selecting any 2 forecast submissions and seeing variance automatically Flexa Tables on AppSource handles it without DAX. Users pick the two periods directly in the published report, variance column appears instantly across all metrics. No LASTDATE or ALLSELECTED maintenance needed: https://appsource.microsoft.com/en-us/product/power-bi-visuals/devspearllc1670524393721.flexa_tables
