cancel
Showing results for
Did you mean:
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?

1 ACCEPTED SOLUTION
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:

Cheers,
Sturla

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

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:

Cheers,
Sturla

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

Announcements

#### 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.

#### 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.

#### 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!

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors