How to get Budget amount from the beginning of the year through current day in Power BI

Question

I need to calculate Projected Premium by Insurance Type. The formula for calculation is: Sum(Budget) - Sum(Budget thru current Day) * (1+BudgetVariance) + TotalPremium

BudgetVariance = (Premium  - Budget tru current Day) / Budget tru current Day

Basically I got all variables I need (Thanks to Joe), but some of them don't calculate properly:

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

BudgetMTD should be 25,882,308 but it displays 30,148,763 (which is the total for 12 months).

Also BudgetVarianceMTD displays correctly for each month, but in a card I need it as:

(Premium  - Budget tru current Day) / Budget tru current Day

Which is:

28,505,823 - 25,882,308 / 25,882,308 = 0.10136326

Ahmad Gozin Ahmad Gozin · Accepted Answer · 2017-11-02T19:50:52

just a hint to get from beginning of the year to current day

 DATEDIFF(month, '2017/01/01', GETDATE()) AS d

How to get Budget amount from the beginning of the year through current day in Power BI

1 Answers