Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

the data is not pivoted

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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