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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi v-lili6-msft ,

         

        In my case i have SKU column in product table and oderdate in sales table and calender date in a calender table.

         

        I am writing measure as follows but it is not working. could you please modify the dax.

         

        Measure  = var Initialdate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date])) return DIVIDE(CALCULATE(SUM(Sales[dw_cust_id]),ALLEXCEPT('Product','Product'[SKU_Name]),FILTER(Sales,Sales[Order_Date]<Initialdate)),CALCULATE(SUM(Sales[dw_cust_id])))
         
        Thanks