Month budget to daily

[Lund]

Hi Everyone

I have this tables with my cutomers budget amount month by month.

But i need to split it up in a messaure, so i get the budget day by day, how can i sovle that?

[sturlaws · Accepted solution]

Hi, @Lund, 

there are numeros ways to achieve what your are after, depending on your scenario. E.g. is it direct query or import? What does your data model look like? Do you want the budget spread over all days of the month, or just workdays? Should you handle national holidays?

Assuming that you have a date table in your model, and you want to divide the monthly budget by number of workdays in the month, you can create a measure like this:

Daily budget =
VAR _date =
    CALCULATE ( SELECTEDVALUE ( DimDate[Date] ) )
VAR _month =
    MONTH ( _date )
VAR _year =
    YEAR ( _date )
VAR _workdays =
    CALCULATE (
        COUNTROWS ( DimDate ),
        FILTER (
            ALL ( DimDate ),
            YEAR ( DimDate[Date] ) = _year
                && MONTH ( DimDate[Date] ) = _month
                && WEEKDAY ( DimDate[Date], 2 ) < 6
        )
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( Budget[Amount] ),
            FILTER (
                ALLEXCEPT ( Budget, Budget[CustomerNo], Budget[code] ),
                YEAR ( Budget[Date] ) = _year
                    && MONTH ( Budget[Date] ) = _month
            )
        ),
        _workdays
    )

Then create a table visual with dates and this measure and you should get something like this:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.