Forum Discussion
Variances between columns
- Anonymous8 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.
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 - FirstForecastNow 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.
Yes Submission month is a calcualted column I created forcing the dates to all be the same... the 1st of the month