cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lund
Frequent Visitor

Month budget to daily

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?

bi 4.png

1 ACCEPTED SOLUTION
sturlaws
Super User
Super User

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:

sturlaws_0-1670447179474.png

 

Cheers,
Sturla

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

 

View solution in original post

1 REPLY 1
sturlaws
Super User
Super User

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:

sturlaws_0-1670447179474.png

 

Cheers,
Sturla

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.