Forum Discussion

santi17's avatar
santi17
Frequent Visitor
3 years ago

Divide by Date slicer

Hi, I need help with this case.

 

 

Stock = Current stock
Sold = Sold value will change following the control date. (This one not the problem)
--

Average Sales is problem.
What I want to do in Average Sales:
If Control Date 7/1/23 - 7/31/23 is 31 Days
Average Sales = Sold/31
If Control Date 7/1/23 - 7/7/23 is 7 Days
Average Sales = Sold/7

Basically, the Control Date will be the divisor for the value Sold and become Average Sales

--

Stock/AVG = Current Stock divine by Average Sales

--

 

 

 

 

5 Replies

  • gregoliveira's avatar
    gregoliveira
    Icon for Helper II rankHelper II
    3 years ago

    Hi!

     

    I can see two ways to achieve the solution

     

    First:

     

    Avg Sales = DIVIDE ( SUM ( Sales ) , COUNTROWS ( Calendar_Table )  )

     

    Second,

     

    Avg Sales = AVERAGEX ( VALUES ( Calendar_Table[Date] ), SUM ( Sale ) )

    • santi17's avatar
      santi17
      Frequent Visitor
      3 years ago

      Hi Grego,

       

      Not working well.
      It's possible to count value Slincer?

      I have try to count range 7/2/2023 tp 7/12/2023 but its count 31..
      I use this mesaure:

      controldate = COUNTROWS(VALUES(itemLedgerEntries[postingDate].[Day]))

      • gregoliveira's avatar
        gregoliveira
        Icon for Helper II rankHelper II
        3 years ago

        No. You will need to create a calendar table. I believe finishing this measure using the automatic date hierarchy will be very hard.

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    to fin the number of days , simply use a countrows function 

    COUNTROWS('table of your slicer[date]))

    then do a sold/[new measure]