Reorder dax calculation

[Anonymous]

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 date of slicer date. i even tried with follwing dax though it is not working : Initial value= CALCULATE(COUNT(Sales[cust_id]),FILTER('Calendar','Calendar'[Date]<FIRSTDATE('Calendar'[Date])))

after getting the initial value i.e. if initial value is 100 outlets for a particular SKU and we will calculate the No of outles repeated for a particular SKU out of those 100 outlets in the given slicer range i.e. between 1/1/2020 to 1/7/2020 are suppose 5 then the reorder will be calculated as 100/5 = 2.

Please help with the dax for the following scenerio.

[v-lili6-msft]

hi  @Anonymous 

For your case, you need to keep the Calendar table has no relationship with Sales table or the relationship is inactive.

And if you still have the problem, please share some sample data and your expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Anonymous]

sample data and expected output.

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 which are in 11 are 7

Reorder value will be 11/7 = 1.58

Thanks

[Anonymous]

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]

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]

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.

[v-lili6-msft]

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]

Hi @v-lili6-msft ,                                                                                                                                                                                             

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

Thanks 

[v-lili6-msft]

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]

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

[amitchandak]

@Anonymous , Can you share sample data and sample output. Please mark me @

[Anonymous]

its better to share sample dataset and expected output so that anyone can answer you easily.

Thanks,

pravin