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.

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super 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.