Salesbudget per month to amount Ytd

[Norbertus]

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,-

[v-ljerr-msft · Accepted solution]

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