cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Calculate monthly budget to daily budget

Hello,

I have a budget table with budgets per month. Columns; Market, MonthNo, District, Budget.

I also have a calendar table, and I have managed to join the budget table to it, and created a new table.

 

But now, in the budget column, I get the monthly budget on each date in a month.

What I need to do is to split the monthly budget over the working days in respective month. I am looking at creating a calculated column, or a measure or something, but I lack knowledge in DAX.

So, the end result should be one column with the monthly budget spread out over the month, and prefferably also a column with a running total for the year.

 

Any help would be deeply appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: Calculate monthly budget to daily budget

Now with support for the hierarchy:

BudgetPerDay = 
var __CurrentMonth = MAX('Date'[YearMonthnumber])
var __Days = CALCULATE(COUNTROWS('Date'); FILTER(ALL('Date');'Date'[YearMonthnumber]=__CurrentMonth && 'Date'[DayOfWeekNumber] <> 7 && 'Date'[DayOfWeekNumber] <> 1))
var __Budget = MIN(Budgets[Budget])
return 
SWITCH (
    TRUE;
     ISINSCOPE ( 'Date'[Date]); IF(MIN('Date'[DayOfWeekNumber])<>1 && MIN('Date'[DayOfWeekNumber])<>7; MINX('Date';__Budget / __Days);BLANK());
     ISFILTERED(  'Date'[YearQuarter]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth) ;
      ISINSCOPE ( 'Date'[YearMonthnumber]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth);
    ISFILTERED(  'Date'[Year]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth)
)

As seen here:

hierarchy.jpg

File has been updated.

 Regards, Steve. 

View solution in original post

8 REPLIES 8
Highlighted
Responsive Resident
Responsive Resident

Re: Calculate monthly budget to daily budget

Hi Chewy

 

Well done on having a calanedar table, that makes things easier.

 

Please conisder this solution and leave kudos

 

Create a new DAX measure  .....

 

Total budget amount =

-- dont budget when calculating or browsing at daily level
IF(
NOT(ISFILTERED('Calendar'[Date])),
SUM('BudgetFile'[Budget amount]))
 
You can display this column or use it in calculations.
the correct budget will display at year, quater and month level but will then hide at day level.
 
 
 
Highlighted
Helper II
Helper II

Re: Calculate monthly budget to daily budget

Hello Speedramps,

like I said, I am not at DAX. I tried your solution and all I got was a total of all monthly budgets, so a yearly budget. I wanted to go the other way, to split the monthly budget over the working days in that month. In my calendar table I have a column for working days (1=working day, 0=non-workingday).

 

Or have I misunderstood you?

 

Highlighted
Solution Specialist
Solution Specialist

Re: Calculate monthly budget to daily budget

Hi,

Here you go:

 

 

 

BudgetPerDay = 
var __CurrentMonth = SELECTEDVALUE('Date'[YearMonthnumber])
var __Days = CALCULATE(COUNTROWS('Date'); FILTER(ALL('Date');'Date'[YearMonthnumber]=__CurrentMonth && 'Date'[DayOfWeekNumber] <> 7 && 'Date'[DayOfWeekNumber] <> 1))
var __Budget = MIN(Budgets[Budget])
return 
IF(MIN('Date'[DayOfWeekNumber])<>1 && MIN('Date'[DayOfWeekNumber])<>7;
MINX('Date';__Budget / __Days);BLANK())

 

 

 

As can be seen here:

budetday.jpg

Please note the data model here:

dmmm.jpg

And the values:

budget4.jpg

File is available here

 

What its doing:

Joining on month, thereby making the budget (on month level) available for all days. 

Then it calculates the daily amount by first:

counting all the working days and then divide the budget over the working days. This value is then displayed on day level, but only on the working days of course

 

Please mark as solution if this works for you. Thumbs up for the effort is appreciated.

 

p.s. you will need an iterator when using the value in other calculations, esp. when aggregating the value.  

 

Kind regards, Steve. 

 

 

Highlighted
Responsive Resident
Responsive Resident

Re: Calculate monthly budget to daily budget

Hi again Chewy

Sorry I gave you the right answer to the wrong question the first time arround. 😁

Please consider this solution and leave kudos.

Create both measures then add a date hierachy and [Dynamic budget amount] to a visual.
As you drill down the date hierachy the budget will swap from monthly to a date value based on the context
and takes account of working days as requested.

 

IsBudgetValid =
-- flag if you are reporting at month or date level
NOT(
ISFILTERED('Calendar'[Date])
|| ISFILTERED('Calendar'[Month])
|| ISFILTERED('Calendar'[MonthNum])
)

 


Dynamic budget amount =
/* DOCUMENTATION *
Converts the yearly budget into the context granularity, for example month or date
*/

IF(
[IsBudgetValid],

-- if the context is at year level, then return the year budget
SUM(Budgets[BudgetAmount]),


-- else calculate (working days in the context / working days in the year) * budget for the year
-- ==========================================================

-- iterate calculation for year in context
SUMX(VALUES('Calendar'[Year]),

-- divide working days in the context by working days in the year to get a ratio
DIVIDE(

-- count the working days in the context
CALCULATE(COUNTROWS('Calendar'),
'Calendar'[WorkingDay] = TRUE
),

-- count the working days in the year
CALCULATE(
COUNTROWS('Calendar'),
ALLEXCEPT('Calendar','Calendar'[Year]),
'Calendar'[WorkingDay] = TRUE
))

-- mutiply the ratio by the budget for the year being iterated
* CALCULATE(
SUM(Budgets[BudgetAmount]),
FILTER(ALL(Budgets[Year]),Budgets[Year]='Calendar'[Year])
)
)
)

Highlighted
Solution Specialist
Solution Specialist

Re: Calculate monthly budget to daily budget

Now with support for the hierarchy:

BudgetPerDay = 
var __CurrentMonth = MAX('Date'[YearMonthnumber])
var __Days = CALCULATE(COUNTROWS('Date'); FILTER(ALL('Date');'Date'[YearMonthnumber]=__CurrentMonth && 'Date'[DayOfWeekNumber] <> 7 && 'Date'[DayOfWeekNumber] <> 1))
var __Budget = MIN(Budgets[Budget])
return 
SWITCH (
    TRUE;
     ISINSCOPE ( 'Date'[Date]); IF(MIN('Date'[DayOfWeekNumber])<>1 && MIN('Date'[DayOfWeekNumber])<>7; MINX('Date';__Budget / __Days);BLANK());
     ISFILTERED(  'Date'[YearQuarter]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth) ;
      ISINSCOPE ( 'Date'[YearMonthnumber]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth);
    ISFILTERED(  'Date'[Year]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth)
)

As seen here:

hierarchy.jpg

File has been updated.

 Regards, Steve. 

View solution in original post

Highlighted
Helper II
Helper II

Re: Calculate monthly budget to daily budget

Sweet!

Thanks Steve, that worked.

 

Regards,

//Peter

Highlighted
Solution Specialist
Solution Specialist

Re: Calculate monthly budget to daily budget

Welcome!

Highlighted
Responsive Resident
Responsive Resident

Re: Calculate monthly budget to daily budget

Hi again Chewy

Please test the quarter option, I don't this it will be correct.

 

The var is immutable meaning that it is calculated once for the context and not recaluated

var __CurrentMonth = MAX('Date'[YearMonthnumber])

 

In quarter 1 it would = 202003 ( March 2020)

 

In which case
ISFILTERED( 'Date'[YearQuarter]); CALCULATE(SUM(Budgets[Budget]);Budgets[Month]=__CurrentMonth) ;

will apply the March 2020 budget x 3 to the quarter,

rather than apply the Jan 2020, Feb 2020 and March 2020 budget to the quarter

 

 

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.