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

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

3 Replies

  • 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
  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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