Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Reorder dax calculation

Hi, slicer is between 1/1/2020 to 1/7/2020 i want to calculate initial value as customer count for a particular SKU between earilest date of calender date and up to day less than than the first dat...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

sample data and expected output is as shown below.

Date            customer/outlet     SKU
22/12/2019       2                         A
23/12/2019       1                         A
24/12/2019       3                         B
25/12/2019       2                         A
26/12/2019       3                         C
27/12/2019       4                         A
28/12/2019       8                         B
29/12/2019       1                         C
30/12/2019       2                         A
31/12/2019       3                         B
1/1/2020           1                         A
2/1/2020           4                         B
3/1/2020           2                         B
4/1/2020           2                         A
5/1/2020           3                         C
6/1/2020           3                         A
7/1/2020           1                         A
8/1/2020           2                         C
9/1/2020           3                         A
10/1/2020         1                         A


when slicer is between 1/1/2019 to 7/1/2019

Initial customer/outlets for A (i.e. before 1/1/2019) is 11

Repeated outlets for A which are in 11 are 7

Reorder value for A will be 11/7 = 1.58

 

Thanks.

 

 

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    HI  Anonymous 

    You could try this measure as below:

    Measure = 
var Initialdate= CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar') ) 
var InitialSKU=CALCULATE(MAX('Table'[SKU]),FILTER('Table','Table'[Date]= Initialdate )) return
DIVIDE(CALCULATE(SUM('Table'[customer/outlet]),FILTER(ALL('Table'),'Table'[Date]<Initialdate&&'Table'[SKU]=InitialSKU)) , CALCULATE(COUNTROWS(ALLSELECTED('Calendar'))))

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lili6-msft ,                                                                                                                                                                                             

      I want Measure value to be populated for all SKU's (A,B,C).

       

      Thanks 

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi   Anonymous 

        If so just adjust the formula as below:

        Measure 2 = 
var Initialdate= CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar') ) 
var InitialSKU=CALCULATE(MAX('Table'[SKU]),FILTER('Table','Table'[Date]= Initialdate )) return
DIVIDE(CALCULATE(SUM('Table'[customer/outlet]),FILTER(ALLEXCEPT('Table','Table'[SKU]),'Table'[Date]<Initialdate)),CALCULATE(SUM('Table'[customer/outlet])))

        Result:

         

        Regards,

        Lin

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    In 2019, for SKU A, there are only 3 customers - 1,2 and 4.  From Jan 1-7 2020, for SKU A, of these 3 customers identified in the earlier period (1,2 and 4), there are only 2 customers - 1 and 2.  So the answer should be 1+2+1 = 4 and not 1+2+1+3 = 7

    Please check.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Ashish_Mathur  Even as per the logic it should be 4..

       

      Anonymoustry below measure,

       

      ReorderValue =
      var init=CALCULATE(SUM(Check[customer/outlet]),FILTER(Check,Check[Date]<MIN('Date'[Date])))
      VAr Init_Outlet=DISTINCT(SELECTCOLUMNS(FILTER(check,Check[Date]<MIN('Date'[Date])),"Init",Check[customer/outlet]))
      VAr Init_Sku=DISTINCT(SELECTCOLUMNS(FILTER(check,Check[Date]<MIN('Date'[Date])),"Init",Check[SKU]))

      var final=
      CALCULATE(SUM(Check[customer/outlet]),FILTER(Check,Check[Date]<=MAX('Date'[Date]) && Check[Date]>=MIN('Date'[Date]) && Check[customer/outlet] in Init_Outlet && Check[SKU] in Init_Sku))
      return
      DIVIDE(init,final,0)

        

      For A it will return 11/4=2.8

      for B 0

      For C 4/3=1.3

       

      Make sure your measure is of type decimal with 2 decimal seprations.

      Else it will return 3,0,1

       

      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.