Forum Discussion
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 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.
11 Replies
Anonymous , Can you share sample data and sample output. Please mark me @
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/38490Regards,
Lin
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/2019Initial 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.
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
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/2019Initial 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
