how to calculate monthly budget till current day in power bi Desktop

Question

I have a budget broken down by month. But I need to calculate it till current day. For example :

    Budget**        PostDate**
Jan  $2,125,889     1/1/2018
Feb  $4,141,659     2/1/2018
Mar  $4,320,698     3/1/2018
Apr  $4,461,430     4/1/2018
May  $4,049,404     5/1/2018
     $19,099,080

The total below is $ 19,099,080 for the whole 5 month of this year. What I need is:

1) calculate the budget per day for current month 4,094,404 / 31 = 132,077.548

2) calculate how many days already passed in current month:

     difference in days between the beginning of the month and today. = 24

3) calculate budget through current day in current month:

     Budget Per Day * Days passed in this month = 132,077.548 * 24 = 
$ 3,169,861

4) Sum up budget for previous 4 month and Budget through current day:

    15,049,676 + 3,169,861 = 18,219,537

Also I have a Year parameter in my report. So the numbers should change depending on a year.

I am trying to make it in one measure using variables. I was able to get number of days in current month:

VAR DaysInCurrentMonth = 
        MAXX(Dates, DAY(EOMONTH(Dates[Date], 0)))

I am stuck on how to get number of days passes in this month. How to substitute "BegginingOfMonth" with the date so I can utilize DATEDIFF function.

VAR DaysPassed=DATEDIFF ( "BegginingOfMonth", EOMONTH ( NOW (), 0 ), DAY )

Also tried to use DATE function, but the year will be dynamic. It wont let me use a year from date table.

UPDATE:

Alexis, thanks for your respond. Simple and clear. The only thing it summarizes till the end of the year, is any way to get sum only till current day?

Thanks

Alexis Olson Alexis Olson · Accepted Answer · 2018-05-24T18:24:40

Here's a month-to-date measure that should add up correctly:

MTD Budget = SUMX(Budget,
                  Budget[Budget] *
                  IF(MONTH(TODAY()) <= MONTH(Budget[PostDate], 1, BLANK()) *
                  IF(MONTH(TODAY()) = MONTH(Budget[PostDate]),
                     DIVIDE(DAY(TODAY()), DAY(EOMONTH(Budget[PostDate],0))), 1))

This pro-rates the budget only for the current month.

how to calculate monthly budget till current day in power bi Desktop

1 Answers