cancel
Showing results for
Did you mean:
Highlighted
Member

## Salesbudget per month to amount Ytd

Hi,

We have a table with the salesbudgets per year / month.

Now we want to calculate the amount with a YtD function into account the workdays in that month

example measure date = 15 januari 2018

budget januar 2018 => €100.000

workdays januari 2018 => 23 day (monday to friday)

workdays to 15 januari 13 days

YtD => €100.000 / 23 * 13 = €56.521,-

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Salesbudget per month to amount Ytd

Hi @Norbertus,

If I understand you correctly, you should be able to follow steps below to create the measure.

1. Add an "IsWorkingDay" column in your Calendar table. If you don't have a Calendar table yet, you'll need create it first.

2. Then use the formular below to create the measure.

```Measure =
VAR currentDate =
MAX ( Date[Date] )
VAR currentYear =
YEAR ( currentDate )
VAR currentMonth =
MONTH ( currentDate )
RETURN
SUM ( 'Table1'[budget] )
* DIVIDE (
CALCULATE (
COUNTROWS ( Date[Date] ),
FILTER (
ALL ( Date ),
YEAR ( Date[Date] ) = currentYear
&& MONTH ( Date[Date] ) = currentMonth
&& Date[Date] <= currentDate
&& Date[IsWorkingDay] = 1
)
),
CALCULATE (
COUNTROWS ( Date[Date] ),
FILTER (
ALL ( Date ),
YEAR ( Date[Date] ) = currentYear
&& MONTH ( Date[Date] ) = currentMonth
&& Date[IsWorkingDay] = 1
)
)
)```

Reference:

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/19/working-days-computation-in-powerpivot.a...

https://blog.andredevelopment.com/post/power-bi-working-days-and-holiday-calendars

Regards

Super Contributor

## Re: Salesbudget per month to amount Ytd

Hi @Norbertus,

If I understand you correctly, you should be able to follow steps below to create the measure.

1. Add an "IsWorkingDay" column in your Calendar table. If you don't have a Calendar table yet, you'll need create it first.

2. Then use the formular below to create the measure.

```Measure =
VAR currentDate =
MAX ( Date[Date] )
VAR currentYear =
YEAR ( currentDate )
VAR currentMonth =
MONTH ( currentDate )
RETURN
SUM ( 'Table1'[budget] )
* DIVIDE (
CALCULATE (
COUNTROWS ( Date[Date] ),
FILTER (
ALL ( Date ),
YEAR ( Date[Date] ) = currentYear
&& MONTH ( Date[Date] ) = currentMonth
&& Date[Date] <= currentDate
&& Date[IsWorkingDay] = 1
)
),
CALCULATE (
COUNTROWS ( Date[Date] ),
FILTER (
ALL ( Date ),
YEAR ( Date[Date] ) = currentYear
&& MONTH ( Date[Date] ) = currentMonth
&& Date[IsWorkingDay] = 1
)
)
)```

Reference:

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/19/working-days-computation-in-powerpivot.a...

https://blog.andredevelopment.com/post/power-bi-working-days-and-holiday-calendars

Regards