Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
speedramps
Super User
Super User

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.
 
 
 
Anonymous
Not applicable

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?

 

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])
)
)
)

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. 

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. 

 

 

Anonymous
Not applicable

Sweet!

Thanks Steve, that worked.

 

Regards,

//Peter

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors