Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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-17Nov-17 
  RevenueRevenueVariance
Forecast MonthAug-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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Ok so that tells me that for each ForecastMonth, the First and Last Date are coming back with the same date.  On the brightside, that suggests that the formula overall is working. Its now a matter of understanding why, on a row by row context, we are only seeing a single Submission Month date.

View solution in original post

18 REPLIES 18
Anonymous
Not applicable

I believe you need to unpivot the data.  Once you do that, making line comparisions will be much easier using Power BI's time intelligence.

Anonymous
Not applicable

the data is not pivoted

Anonymous
Not applicable

Can you post an example of the raw data, rather than the pivoted data in that table?

Anonymous
Not applicable

Here is a sample of the data table. I have stripped out alot of other columns not relevant.

 

Submission is the month they submittedthe forecast (this is across many site and many different contracts).

ForecastMonth the the months they are actually forecast revenue etc. The full table is large because multiple sites lodge multiple forecasts for multiple contracts.

 

My matrix table in Power BI already allows the end user to select 2 different submission and it displays forecast by each forecast month and I need to get a variance between forecasts (where both submission have forecasts for same months)

 

SubmissionForecastMonthSubmissionDateForecast RevenueForecast CostsForecast EBIT
May-17Jan-1811/05/2017 0:00$1,466,233.25$1,384,001.68$82,231.57
May-17Feb-1811/05/2017 0:00$1,501,522.70$1,270,457.32$231,065.38
May-17Mar-1811/05/2017 0:00$1,719,481.61$1,432,636.41$286,845.20
May-17Apr-1811/05/2017 0:00$1,527,356.19$1,368,436.78$158,919.41
May-17May-1711/05/2017 0:00$68,981.00$62,123.00$6,858.00
May-17May-1711/05/2017 0:00$200,214.66$146,207.53$54,007.13
May-17Jun-1711/05/2017 0:00$272,591.68$116,351.76$156,239.92
May-17Jul-1711/05/2017 0:00$210,608.62$104,090.21$106,518.41
May-17Aug-1711/05/2017 0:00$229,882.14$92,260.93$137,621.21
May-17Sep-1711/05/2017 0:00$196,375.99$76,575.14$119,800.85
May-17Oct-1711/05/2017 0:00$176,739.40$65,753.22$110,986.18
May-17Nov-1711/05/2017 0:00$137,618.94$53,922.15$83,696.78
May-17Dec-1711/05/2017 0:00$94,052.70$42,237.35$51,815.35
May-17May-1711/05/2017 0:00$725,995.24$619,507.54$106,487.71
May-17Jun-1711/05/2017 0:00$789,556.54$626,465.64$163,090.90
May-17Jul-1711/05/2017 0:00$756,131.12$658,472.07$97,659.05
May-17Aug-1711/05/2017 0:00$792,565.81$705,248.32$87,317.48
May-17Sep-1711/05/2017 0:00$767,902.42$709,032.08$58,870.34
May-17Oct-1711/05/2017 0:00$775,464.60$689,307.78$86,156.82
May-17Nov-1711/05/2017 0:00$806,096.63$709,027.77$97,068.87
May-17Dec-1711/05/2017 0:00$695,499.97$674,719.24$20,780.73
May-17Jan-1811/05/2017 0:00$747,766.39$647,761.87$100,004.52
May-17Feb-1811/05/2017 0:00$717,765.18$597,342.74$120,422.44
Anonymous
Not applicable

What about something along the lines of below?  The idea is that we take the first and last date of the selection and calculate the sum total for each submission date.  We will be using the measure inside your matrix thus we can rely on row context to further constrain.  After this calculates the First and Last date's forecast sum, we then take the Last minus the First for the variance.

 

Variance = Var StartDate = FIRSTDATE('YourTable'[SubmissionDate])
Var EndDate = LASTDATE('YourTable'[SubmissionDate])
Var FirstForecast = CALCULATE(
	Sum('YourTable'[Forecast Revenue},
	'YourTable'[SubmissionDate] = StartDate
)
Var LastForecast = CALCULATE(
	Sum('YourTable'[Forecast Revenue},
	'YourTable'[SubmissionDate] = EndDate
) 
RETURN

LastForecast - FirstForecast
Anonymous
Not applicable

Ok so this is not working its delivering zero variances.

 

I realised there are a few extra things I need to share. We have 2 forms of forecasts. 1. what we term a "Rolling Forecast" which is the projected forecast in future months and 2 a Monthly Forecast which is a more detailed forecast of the current month. Both these forecasts are in the data table and we have a column that identifies the submission type (whether is be a rolling forecast or monthly forecast)

 

I have a measure set up with gives me just rolling forecast revenue

RF Revenue = CALCULATE(sum('DPLImportPBI'[Forecast Revenue]),filter('DPLImportPBI',DPLImportPBI[SubmissionType]="Rolling Forecast"))

 

However in your solution above I couldn't replace DPLImportPBI[Forecast Revenue] with DPLImportPBI[RF Revenue] cause RF Revenue is a measure.

 

Also submission dates are not consistant (some centres submit a day early or a day late) so I had to reference to Submission Month because the date here is consistently the 1st of the month. (note some centres share contracts with other centres so I need consistency when calculating contract level forecasts which come from multiple centre forecasts)

 

So I tried below but its still giving zero as the variance

 

 

Revenue Variance = Var StartDate = FIRSTDATE('DPLImportPBI'[Submission Month]) Var EndDate = LastDate ('DPLImportPBI'[Submission Month]) Var FirstForecast = CALCULATE(sum('DPLImportPBI'[Forecast Revenue]),filter('DPLImportPBI','DPLImportPBI'[SubmissionType]="Rolling Forecast"),'DPLImportPBI'[Submission Month]=startdate) Var LastForecast = CALCULATE(sum('DPLImportPBI'[Forecast Revenue]),filter('DPLImportPBI','DPLImportPBI'[SubmissionType]="Rolling Forecast"),'DPLImportPBI'[Submission Month]=EndDate) return LastForecast - FirstForecast

Anonymous
Not applicable

First thing i've noted is that your 'Filter' statements aren't required within the calculate statement.  So your code can be updated to simply be:

Revenue Variance = Var StartDate = FIRSTDATE('DPLImportPBI'[Submission Month]) 
Var EndDate = LastDate ('DPLImportPBI'[Submission Month]) 
Var FirstForecast = CALCULATE(
	sum('DPLImportPBI'[Forecast Revenue]),
	'DPLImportPBI'[SubmissionType] = "Rolling Forecast",	
	'DPLImportPBI'[Submission Month] = startdate
) 
				
Var LastForecast = CALCULATE(
	sum('DPLImportPBI'[Forecast Revenue]),	
	'DPLImportPBI'[SubmissionType] = "Rolling Forecast",
	'DPLImportPBI'[Submission Month] = EndDate
) 

return LastForecast - FirstForecast

Now this code does assume that the all of your forecasts that you want to group will have the same date when we make the 'Submission Month' comparion.  Typically my solution to line dates up is to force all of the dates to become the 1st of that month/year using DATE(YEAR([Column], Month[Column], 1).  You have mentioned that your submissions can be early, so you might need to solve what you do if your submission dates fall in the previous month.  One solution might be to take the submission date and add a certain number of days (lets say 5 days), before resolving the date to the first.  This means that anything submitted in the last few days is pushed into the new month, prior to us resolving our date change to become the First.

 

One approach to doing this might be:  Create a new column called "Submission Month Group" which is equal to 'Submission Month' + 5 days, then converted to the first.  We could then use that new column in our formula above.  If your Submission Month is already a calculated column, you could incorporate that idea in that existing Submission Month formula.

Anonymous
Not applicable

Still returning zeros as the variances

Anonymous
Not applicable

For trouble shooting purposes, create a new measure with the same code that instead of having "LastForecast - FirstForecast" change it to "LastForecast".  Put that measure into your visuals and see what numbers come up.  Now measure that part to "FirstForecast", also do a similar check with 'StartDate' and 'EndDate'

 

Are you getting some numbers or are you getting 0?  Are the Start and End Dates coming up with the correct values?

 

If 0:

Check the values in column 'SubmissionType', are the values exactly "Rolling Forecast", including that capitalization and no extra spaces?

 

 

Anonymous
Not applicable

Yep was already testing in that way

 

both FirstForecast and LastForecast are producing the same numbers (hence the zeros).

 

Here is a copy of the visualisation with the return just being the LastForecast. Note in the submission filter in this example i have selected July 2017 and November 2017

PBI Screenshot.PNG

Anonymous
Not applicable

Sorry yhat last visual was the zeros I was referring to... Here is with just returning LastForecast

PBI Screenshot.PNG

Anonymous
Not applicable

Ok so that tells me that for each ForecastMonth, the First and Last Date are coming back with the same date.  On the brightside, that suggests that the formula overall is working. Its now a matter of understanding why, on a row by row context, we are only seeing a single Submission Month date.

Anonymous
Not applicable

Can I ask another related question. I now have the measures working but due to the set up the variances measure I am having to put these measures on a separate visual to the forecasts (see screenshot)

 

You can see I have a matrix displaying my July and October forecast submissions and I have a second visual doing the variance between the 2 forecasts (per per solution given for revenue I just replicated for EBIT and Margin %). This vall works fine but ideally I would be better on one visual. Can you think of a possible way?

PBI Screenshot.PNG

Anonymous
Not applicable

I feel like you can by using ALLSELECTED and potentially using another variable to remember the forecast month row context which i'm expecting will be lost when ALLSELECTED is used.

 

To make use of ALLSELECTED, it would look something like:

LastForecast = CALCULATE(
	sum('DPLImportPBI'[Forecast Revenue]),
	ALLSELECTED('DPLImportPBI'),
	'DPLImportPBI'[SubmissionType] = "Rolling Forecast",
	'DPLImportPBI'[Submission Month] = EndDate
) 

I'm expecting if you use the above as is, your forecast month row context could be lost. If thats the case, just add a new variable in your formula with LASTDATE and that column, then put it into the calculate formula as another filter condition along with the 3 in the above.

Anonymous
Not applicable

Sorry this doesn't achieve what I'm looking for and maybe I didn't explain correctly.

 

For started the ALLSELECTED option pulls in all revenuve for all forecasts and I definitley do not want that.

 

The main issue I have it that the matrix visual has a 2 tiered colum heading. It has July 2017 and November 2017 (becasue these are the 2 submission months I have chosen in my slicer. Below these are the column headers for my measues (RF Revenue, RF EBIT & RF Margin).

 

When I add the varaince measures it tries to give a variance for the July forecast and also a varaince for the November forecast. I do not want this... I want one varaince comparing the 2 forecast. This works perfectly ok as a seperate visual but not if I try and add it to the existing visual

Anonymous
Not applicable

ALLSELECTED is likely the avenue you'll need to persist with, but it won't be enough on its own. As you have correctly pointed out, it will strip extra context away.  You can first record this context into additional variables and add back into your calculate statement.  It will just simply take some trial and error for you to get right.

 

Really comes down to the choice of whether the 2 visuals are going to be enough or whether you wish to spend the time and effort in adding additional complexity in order to squeeze it into the single visual.  Its a bit hard to solve from afar so its one of those problems you will need to pick up and run with.

 

Anonymous
Not applicable

Think i got it working... just re typed the LASTDATE function and it worked. Must of been a typo somewhere

Anonymous
Not applicable

Yes Submission month is a calcualted column I created forcing the dates to all be the same... the 1st of the month

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.