Calculate and divind values by working days

[Guilher_Moura19]

Hi, everyone

I have a table with CALENDARAUTO( ) and i added a column WorkDay = 1 when true, 0 when false

Using another table, i have the sale day and the price of the sale, but i'd to another columns that says a big value of "month sales expectation" that is declared in the first day of the month.

But the other days is declared by 0. 

How can i replicate or create a measure/column to take this Total of Month Expectation divided by the count of Working Days in equal parts, to show something like this?

I create this table on excel and i divide the total of sales manually by the count of the Working days.

Thank you all,

Best Regards

[MarcoPessina · Accepted solution]

Hello @Guilher_Moura19 ,

you can also try this measure:

CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
ALL ( 'Date' ),
MONTH ( 'Date'[Date]) ? MONTH ( SELECTEDVALUE ( 'Date'[Date] )
&& Year ( 'Date'[Date] ) - Year ( SELECTEDVALUE ( 'Date'[Date] )
&& WEEKDAY('Date'[Date]) <> 6 && WEEKDAY('Date'[Date]) <> 7
)
)
VAR SalesExpMonthValue ? CALCULATE(
max(Sales[MonthSalesExp]),PARALLELPERIOD('Date'[Date],0,MONTH))
Return
DIVIDE(SalesExpMonthValue,WorkingDaysMonth,0)

I hope it helps.

Bless you

Frame of reference

[Guilher_Moura19]

Thank you, @MarcoPessina 

I ran your code and got some errors, but I made some minor changes to the logic and everything went well.

Thank you!

Best regards,

Guilherme Moura

[Mariusz]

Hi @Guilher_Moura19 

Try something like this.

Sales split witin Month = 
VAR __valueToSplit = 
    CALCULATE(
        [Sales],
        ALL( 'Calendar' ),
        VALUES( 'Calendar'[Year Month] )
    )
VAR __countWorkingDays = 
    CALCULATE(
        COUNTROWS( 'Calendar' ),
        ALL( 'Calendar' ),
        VALUES( 'Calendar'[Year Month] ),
        VALUES( 'Calendar'[Is Weekend] ),
        KEEPFILTERS( 'Calendar'[Is Weekend] = FALSE() )
    )
RETURN 
DIVIDE( __valueToSplit, __countWorkingDays )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn