Need help with DAX Calculation for Sales Data

[hanselnunes]

Hello, community,

I have three tables (Daily Sales, Store master, Item Master) with relationships established in between.

Daily Sales: Contain daily aggregated sales of each store product-wise for 2 years, sample as shown below

Date	Store	Product	Quantity	Amount
1/12/2020	Store 1	P1	3	3000
1/12/2020	Store 1	P2	1	100
1/12/2020	Store 2	P1	2	2000
2/12/2020	Store 1	P1	1	1000
2/12/2020	Store 2	P2	3	300
3/12/2020	Store 3	P1	1	1000
3/12/2020	Store 1	P3	5	50

Store Master: Contains Store name, address, lat-long, store type, Open date for all stores present

Store Name	Address	Lat Long	Store Type	Opening Date	Age of the Store
Store 1	Address 1		Mall	1/1/2010	9.9
Store 2	Address 2		Showroom	1/1/2019	1.9
Store 3	Address 3		Mall	1/1/2020	0.9

Item Master: Contains all product details

Item	Gender	Color	Size
P1	Gents	Black	L
P2	Ladies	Blue	M
P3	Kids	White	S

I have a drop-down date filter set with a hierarchy(Year, Quarter, Month).

When a user selects a Month or a Quarter in the filter, I need to find which store was closed for more than 5 consecutive days in that particular Month or Quarter.

Closed Store: If there are no sales for a store on a particular day it will be absent in the Daily Sales table and the Store is marked as closed for that day eg: in the above Daily Sales table Store 3 was closed for 1st and 2nd December because no sales data is present on that days.

So, if the Store is not closed for more than 5 consecutive days in that particular Month or Quarter and the age of the store is more than 1 year it will be marked as "Flag1" else as "Flag2" 

Then I'll be doing a %Growth in sales by comparing the sales for that Month or Quarter with the sales for the same period last year.

This %Growth calculation will be for stores in both Flag1 and Flag2, Product wise.

The final result will look like this:  CY= Current Year, LY = Last Year

Product	CY Sale Flag1	CY Sale Flag2	LY Sales Flag1	LY Sales Flag2	%Growth Flag1	%Growth Flag2
P1
P2
P3

 Can anyone please help me with these calculations in DAX?

Thanks and Regards,

Hansel

[v-shex-msft · Accepted solution]

HI @hanselnunes,

According to your sample data, It seems like some of the stores not have corresponding records.

If this is a case, you not able to mark tags to them because they not really exist in the table, and you not able to use the calculated expressions to interact with 'blank' records. 

BTW, current power bi not able to create dynamic calculated column/tables based on the slicer/filter effect.  You can consider using measures to check records and return results but current measures can't use as a category on visuals.

For this scenario, you can create a table with all of the flags and use it as column on your matrix. Then you can create two measure formulas to calculate CY and PY results based on matrix row and category group and use if statement with switch function of switch between different row content level.

DAX – Making the “Case” for SWITCH() 

Clever Hierarchy Handling in DAX 

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[hanselnunes]

Thank you for the information, will try this.

[v-shex-msft]

HI @hanselnunes,

Did these here for your scenario? If you still confused about the coding formulas or other additional requirements, you can post here with detailed descriptions.
Regards,

Xiaoxin Sheng