How to calculate Projected YTD in Power BI using DAX

3
votes

I have a report in SSRS with calculations that I need to translate into DAX. The goal is to calculate ProjectedYTD

enter image description here

But in order to do that, first I have to calculate Premium and Budgets through current day. So the way I do it in SSRS is:

1) I calculate the budget per day for current month by dividing the total budget for current by number of days in this month. So the budget for October per day is

2,579,757 / 31 = 83,217.97.

2) Next I multiply Budget per day by the number of days between the beginning of the current month till today. So if today is October 27 then

83,217.97 * 27 = 2,246,885

3) And the last step is I adding the whole budget previous to current month (October) to Budget for October till today: 

23,385,769 + 2,246,885 = 25,632,654

Then I calculate Variance per each month and the Total till today.

Variance = (Total Premium - Budget till current day)/ Budget till current day * 100

Which is:

 (28,477,194 - 25,632,654) / 25,632,654 * 100 = 11.1%

And finally to calculate Projected YTD:

(Total Budget for whole year  -  Budget till current day) * (1 + Variance) + TotalPremium

Which is:

(30,148,763 - 25,632,654) * (1 + 0.11) + 28,477,174 = 33,494,470

So I am assuming I have to create all those variables in Power BI, then perform the calculation.

In Power BI I see it something like that:

enter image description here

.ipbx file can be accessed here:

https://www.dropbox.com/s/io1yumnqlrzj067/PremiumByDivisions.pbix?dl=0

Please help.

UPDATE 1

Joe, thank you very much for your time. I think it doesnt calculate correctly the BudgetTillCurrentDay. On a screenshot its 25,965,526, which is sum of Budget till the end of October.

enter image description here

But I need till today. So if today is 30-th, then 2,579,757 /31 * 30 = 2,496,539 --this is Budget from the beginning of the October till today (30th October)

Then Sum(Whole Budget for the Year till the end of September) + 2,496,539 = 25,882,308.

enter image description here

Sorry for confusion.

1
excel-formulapowerbidaxpowerquery

1 Answers

2
votes

Some comments before getting into my solution:

  • The numbers in the file you shared don't exactly line up with the numbers in your question, so hopefully this solution matches the expected numbers.
  • There is a screenshot at the end of this solution showing the report page that I was working on so that you can see the structure I was using as an example.
  • To comment on the last remark in your question, you don't technically have to make all of these measures; you could combine the formulas into one (similar to what I did on the first budget related formula below), but I think breaking them out into separate measures as I did will be nicer for reporting efforts.

Before anything with budgets, I created a couple of measures in the Premiums table to help simplify other formulas later.

PremiumsMTD = TOTALMTD(SUM(Premiums[Premium]), Dates[Date])

PremiumsYTD = TOTALYTD(SUM(Premiums[Premium]), Dates[Date])

First, to create a month to date budget measure, I used the following formula. Step-by-step, this formula 1) figures out the total number of days in the month, 2) divides the total budget for the month by the number of days, 3) figures out how many days have passed in the month (all if a past month, 0 if a future month, the current date for the current month), and finally 4) multiplies the budget per day by how many days have passed.

BudgetMTD = 
VAR DaysOfMonth = MAXX(Dates, DAY(EOMONTH(Dates[Date], 0)))
VAR BudgetPerDayForMonth = SUM(BudgetData[Amount]) / DaysOfMonth
VAR DaysInMonthToToday = MAXX(Dates,
    IF(Dates[Date] < TODAY(), DAY(Dates[Date]),
        IF(Dates[Date] > TODAY(), 0,
            DAY(TODAY())
        )))

RETURN BudgetPerDayForMonth * DaysInMonthToToday

Next, for YTD, simply use Power BI's built-in TOTALYTD function (like was done for the Premiums measures) with the newly created BudgetMTD measure as the expression and filter it to non-future days.

BudgetYTD = TOTALYTD(BudgetData[BudgetMTD], Dates[Date], Dates[Date] <= TODAY())

And then you can get variances by doing simple math using the MTD and YTD measures created before.

BudgetVarianceMTD = (Premiums[PremiumsMTD] - BudgetData[BudgetMTD]) / BudgetData[BudgetMTD]

BudgetVarianceYTD = (Premiums[PremiumsYTD] - BudgetData[BudgetYTD]) / BudgetData[BudgetYTD]

Here is the report page that I was working off of. Notice that I was using a matrix (though it could a number of different visuals), with MonthName as the axis/categories. Since the visual is at a month grain, the YTD values don't have a good place to exist within the same visual (the subtotals Power BI does automatically won't match with the custom measures), which is why there are the cards with the YTD values.

Working report