cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Norbertus Member
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
v-ljerr-msft Super Contributor
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. Smiley Happy

 

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

1 REPLY 1
v-ljerr-msft Super Contributor
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. Smiley Happy

 

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