Divide Weekly Budget by Working Days for Daily Phasing

[joshua1990]

Hello everybody!

I have to divide our weekly budget by the number of working days to get the daily budget.

The Budget table is linked with the YearWeek und Area Key to our Item Master and calendar table.

This Budget table has this structure:

Area - YearWeek - Budget

Now I need a measure that counts the number of working days for the corresponding week. This value should be used as a Denominator, right?

The calendar table contains the column "IsWorkingday" (0 or 1).

In addition, a non Working day should have no daily budget ( = 0 or Blank).

How would you solve that?

In the end I need also to use these daily budgets for a running total per week.

[Fowmy]

@joshua1990 

Can you share some sample data from the budget and calendar table along with the expected output for verification? This way, it will make it clear and easy for a quick solution.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[joshua1990]

Of course! Thank you so much for your support!

Budget Table

Area	YearWeek	Budget
A1	2020-01	500
A1	2020-02	750
A1	2020-03	650
A1	2020-04	500

Calendar Table

Date	Fiscal Week	Year	YearWeek	Fiscal Month	IsWorkingday
31.12.19	01	2020	2020-01	Jan	0
01.01.20	01	2020	2020-01	Jan	1
02.01.20	01	2020	2020-01	Jan	1
03.01.20	01	2020	2020-01	Jan	1

[Fowmy]

@joshua1990 

Please find attached files Excel and PBIX. I have used Power Query to allocate the budget across dates:

https://1drv.ms/u/s!AmoScH5srsIYgYIraRzionKTxQjWgw?e=mIivjQ

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[joshua1990]

Thank you so much!

But I am looking for an approach based on dax measures.

How would you do that?

@Fowmy 

[Fowmy · Accepted solution]

@joshua1990 

I have done the allocation of weekly budget amounts across dates based on the calendar using DAX as well. You will find both Power Query and DAX solutions in the same PBIX.

https://1drv.ms/u/s!AmoScH5srsIYgYIvB21vMVZ_J-WyYA?e=Yp33VU

Budget Allocation DAX = 

VAR T1 = 
    SELECTCOLUMNS(
        'Calendar',
        "DATE", 'Calendar'[Date],
        "Year-Week", 'Calendar'[YearWeek],
        "Week Day", 'Calendar'[IsWorkingday]
    )
VAR T2 = VALUES(Budget[Area])        
VAR T3 = CROSSJOIN( T1, T2)
RETURN
ADDCOLUMNS(
    T3,
    "Budget", 
        VAR BGT = 
            CALCULATE(
                SUM(Budget[Budget]),
                Budget[Area]=EARLIER([Area]),Budget[YearWeek]=EARLIER([Year-Week])
            )
        VAR EDAYS = 
            CALCULATE(
                SUM('Calendar'[IsWorkingday]),
                ALLEXCEPT('Calendar','Calendar'[YearWeek])               
            )            
        RETURN
        DIVIDE( BGT, EDAYS) * [Week Day]
) 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[joshua1990]

Thank you so much @Fowmy ! This is exactly what I am looking for.

Unfortunately, it is not working. I guess it is because of one thing:

All my tables are linked through a date or area key. 

There are numerous measures based on this structure/ relationship.

Is there any chance to adapt your approach with active relationships?

[Fowmy]

@joshua1990 

.

Glad you you like!

You need to connect your DATES table to this new budget allocation table using the date filed, do the same for AREA. Your model will work. You need to create measures for budget Amounts 

Please go ahead and accept this solution as I built  it as per your initial requirement. 

You may open a new ticket for your current request. 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn