Running total for Daily Budget based on DIVIDE

[joshua1990]

Hello everyone!

First of all, I am looking for a DAX measure. No additíonal tables or PQ. I would really like to solve the challenge just with DAX measures.

I am looking for a Running Total measure for weeks.

I have 3 tables  - within this context.

Budget table

YearWeek (key)	Area (key)	Budget
2020-01	A	5000
2020-02	A	4500

ItemMaster

Area (key)	Department
A	Drilling

Calendar

Date	Fiscal Year	Fiscal Week	YearWeek (key)
30.12.2019	2020	01	2020-01
31.12.2019	2020	01	2020-01

These tables are linked together through the corresponding key/ column.

Since the Budget table is based on weekly budget targets, I have to divide these by the number of working days per week.

I did this with the following measure:

# Working Days per Week = 
CALCULATE (
    SUM ( 'Calendar'[IsWorkingDay] ),
    ALL ( 'Calendar' ),
    VALUES ( 'Calendar'[Fiscal Week] ),
    VALUES ( 'Calendar'[Fiscal Year] )
)

Therefore the result looks like this:

Date	Budget	Working Days
01.01.20	5000	5
02.01.20	5000	5
03.01.20	5000	5
04.01.20	5000	5
05.01.20	5000	5
06.01.20	4500	4
07.01.20	4500	4
08.01.20	4500	4
09.01.20	4500	4
10.01.20	4500	4
11.01.20	4500	4
12.01.20	4500	4

With a measure like 

Budget RT = DIVIDE([Budget], [# Working Days per Week])

to get the daily budget the result looks like this:

Date	Budget	Working Days	Daily Budget
01.01.20	5000	5	1000
02.01.20	5000	5	1000
03.01.20	5000	5	1000
04.01.20	5000	5	1000
05.01.20	5000	5	1000
06.01.20	4500	4	1125
07.01.20	4500	4	1125
08.01.20	4500	4	1125
09.01.20	4500	4	1125
10.01.20	4500	4	1125
11.01.20	4500	4	1125
12.01.20	4500	4	1125

You can see the first mistake. Non-working days still get a value.

How can I solve that?

Then I would like to get a running total.

I have built this measure, but it is not working:

Test = 
CALCULATE (
    [Budget RT],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Fiscal Year]
            = MAX ( 'Calendar'[Year] )
            && 'Calendar'[Date]
                <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Fiscal Month Number]
                = MAX ( 'Calendar'[Fiscal Month Number] )
            && 'Calendar'[Fiscal Week]
                = MAX ( 'Calendar'[Fiscal Week] )
    )
)

This measure is not accumulating the values.

Why is that so?

[mahoneypat]

I think this could be simplified overall, but in your measure, if you are trying to have a running total go beyond the week or month, you also need to put "<=" for 'Calendar'[Fiscal Week] or 'Calendar'[Fiscal Month]. 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat]

If that doesn't fix it, here is a measure expression that I think gets your desired results (see pic).  It didn't turn out to be as simply as I first thought, since you are showing day granularity when both working days and budget are in week granularity.  This assumes you have a Many:1 relationship between the Date[YearWeek] and Budget[YearWeek] columns.

Budget Running Total Week =
VAR thisweek =
    MIN ( 'Date'[YearWeek] )
VAR workingdaysthisweek =
    CALCULATE (
        SUM ( 'Date'[Working Day] ),
        ALL ( 'Date' ),
        'Date'[YearWeek] = thisweek
    )
VAR budgetthisweek =
    CALCULATE ( MIN ( Budget[Budget] ), 'Date' )
VAR dailybudget = budgetthisweek / workingdaysthisweek
VAR workingdaycount =
    COUNTROWS (
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
                && 'Date'[Working Day] = 1
                && 'Date'[YearWeek] = thisweek
        )
    )
RETURN
    workingdaycount * dailybudget

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat