Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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:
File has been updated.
Regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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 =
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:
File has been updated.
Regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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:
Please note the data model here:
And the values:
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Sweet!
Thanks Steve, that worked.
Regards,
//Peter
Welcome!
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |