find how many store dont buy a product as required conditions

[MichaelJackpbi]

Hi friends,

I have 2tables Order( ORderdate, Product_ID, Quantity, Store_ID)

and table Store(Store_ID, StoreName)

I need to count how many store that didnt order Product"A" 2 consecutive days from the fist day of month up to today!.

I think that i need to use datebetween function to calculate the days between 2 order times of each store but i am unable to do that because a store can buy product"A" in different dates and i didnt know the startdate and enddate !...

Could anyone give me some advices on my issue?

Regards,
J.

[dkay84_PowerBI]

This one is a little tricky to work through conceptually.  Can you provide some sample data (csv or excel format)?

Thanks

[MichaelJackpbi]

OrderDate	Product	Qty	Store
2/12/2016	A	12	3
2/12/2016	A	11	3
3/12/2016	A	22	3
6/12/2016	A	5	3
7/12/2016	A	2	3
4/12/2016	A	23	2
5/12/2016	A	3	2
6/12/2016	A	31	2
7/12/2016	A	223	2
2/12/2016	A	21	1
3/12/2016	A	23	1
4/12/2016	A	2	1
5/12/2016	A	12	1
6/12/2016	A	123	1
7/12/2016	A	124	1

Hi @dkay84_PowerBI

I can not attach the excel file, i uploaded a image that capturing my excel file. In my sample, i filter Product to "A" only and sort the Store. In fact, my boss needs every store has to sell a product everyday in a selected month.

I.e. This month he selectes product A to put into the strategy, so at the end of month i has to capture stores that order product A everyday and stores that didnot order product A 2 consecutive days.

For my example- assuming 7/12/2016 is the last of month, you can see that Store 3 ordered product A in day 2,3 and day 6/7 --> it means store 3 was not meet the condition because it was not order A in day 4 and day 5

Store 2 ordered product A in day 4/5/6/7 --> it also was not meet the condition because it did not order in day 1,2,3

Store 1 ordered product A in all of day( day 1,2,3,4,5,6,7) --> store 1 meet the condition

__result : I need some reports as:

	Store	Days didn’t  order A			days didn’t order A
Don’t meet	3	2			1day	2 days	3days	4days	>4days
	2	3		0	1	1	0	0
Total	2
meet	1	0
Total	1	0			days didn’t order B
					1day	2 days	3days	4days	>4days
					0	1	1	0	0
					days didn’t order C
					1day	2 days	3days	4days	>4days
					0	1	1	0	0

[dkay84_PowerBI]

Two additional questions:

1.  Is the 30 day period rolling or by month?

2.  Is "not ordering for 2 consecutive days" the same as ordering every day?  Or can a store miss one day and that is okay?

[MichaelJackpbi]

@dkay84_PowerBI

I'd like to answer you as:

1. It depends on the first day of month end currentdate! we just care about current month .

2. Yes! we capture stores that miss more than 2 days at least, and miss days but not consecutive is okay!

Looking for your advice 🙂

Regards,
J.

[MichaelJackpbi]

Has anyone help me  ?:(

[v-sihou-msft · Accepted solution]

@MichaelJackpbi

According to your description, you want to filter Stores with no orders on specific product in 2 consecutive days each month. Right?

Firstly, please summarize your table to avoid duplicate dates for same Store. You can either use SUMMARIZECOLUMNS() function or "Group On" in Query Editor.

We can create a calculated column to identify if contains "exceeding 2 consecutive days" within a Group. Please refer to formula below:

IsNotMeet = 
IF(
DAY(Table2[OrderDate])=1 || DAY(Table2[OrderDate])=2,
0,
IF(
LOOKUPVALUE(Table2[Qty],Table2[OrderDate],Table2[OrderDate]-1,Table2[Store],Table2[Store]) = BLANK() 
&& 
LOOKUPVALUE(Table2[Qty],Table2[OrderDate],Table2[OrderDate]-2,Table2[Store],Table2[Store]) =BLANK(),
1,0)
)

Then you can just filter the data where IsNotMeet is not zero.

Another approach is getting the MAX date before the current date. You can add a calculated column like below:

Max Date Before Current Ddate = 
IF(
CALCULATE(MAX(Table2[OrderDate]),FILTER(Table2,Table2[OrderDate]<EARLIER(Table2[OrderDate]) && Table2[Store]=EARLIER(Table2[Store]) && Table2[Product]=EARLIER(Table2[Product])))=BLANK(),
DATE(YEAR(Table2[OrderDate]),MONTH(Table2[OrderDate]),1),
CALCULATE(MAX(Table2[OrderDate]),FILTER(Table2,Table2[OrderDate]<EARLIER(Table2[OrderDate]) && Table2[Store]=EARLIER(Table2[Store]) && Table2[Product]=EARLIER(Table2[Product]))))

Then just compare the different between the Order Date with the "Max Date Before Curernt Date" to determine if exceeds 2 consecutive days, this calcualted column works same as "IsNotMeet".

IsNotMeet2 = IF(DATEDIFF(Table2[Max Date Before Current Ddate],Table2[OrderDate],DAY)>=2,1,0)

Regards,