Forum Discussion

Guilher_Moura19's avatar
Guilher_Moura19
Frequent Visitor
6 years ago
Solved

Calculate and divind values by working days

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 anothe...
Need Help
  • MarcoPessina's avatar
    MarcoPessina
    6 years ago

    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

MarcoPessina's avatar
MarcoPessina
Icon for Resolver IV rankResolver IV
6 years ago

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's avatar
Guilher_Moura19
Frequent Visitor
6 years ago

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