Same Store Logic based on Filters - Year, Month, MTD/QTD/YTD

[danny0428]

Hi All,

Having trouble coming up with Same Store Logic (comparing stores that have the same amount of days in the period selected – Year, Month, & MTD or QTD or YTD). Examples below. Thank you.

Other Criteria:

Store needs to be opened 1 full year from Opened Date (and currently not Closed based on Date selection) to be considered Same Store

Example 1:

Store: 123

Opened Date: 1/1/18

Closed Date: None

Year	Month	MTD/QTD/YTD	Expected Result - Same Store?	Comment
2019	January	MTD	Yes	31 Days in both periods
2019	January	QTD	Yes	90 Days in both periods
2019	January	YTD	Yes	365 Days in both periods

Example 2:

Store: 456

Opened Date: 1/2/18

Closed Date: None

Year	Month	MTD/QTD/YTD	Expected Result - Same Store?	Comment
2019	January	MTD	No	Only 30 Days for 2018, 31 Days for 2019
2019	February	MTD	Yes
2019	March	QTD	No	Only 89 days for 2018, 90 for 2019
2019	April	QTD	Yes
2019	January	YTD	No	Only 364 days for 2018, 365 for 2019
2020	January	YTD	Yes

Example 3:

Store: 789

Opened Date: 1/3/22

Closed Date: None

Year	Month	MTD/QTD/YTD	Expected Result - Same Store?
2022	December	YTD/QTD/MTD	No
2023	January	MTD	No
2023	February	MTD	Yes
2023	Jan - March	QTD	No
2023	April	QTD	Yes
2023	January	YTD	No
2024	January	YTD	Yes

[amitchandak · Accepted solution]

@danny0428 , with help from a seperate date table, create measures like below. example for MTD. It show value if store opened for all dates

M1= Calculate(distinctcount(Table[Date]), datesmtd('Date'[Date]))
m2 = Calculate(distinctcount(date[Date]), datesmtd('Date'[Date]))
m3= Calculate(sum(Table[value]), datesmtd('Date'[Date]))

m4 = if([M1]= [M2], [M3], blank())

or you can date diff in M1

M1= Calculate(datediff(min(Table[Date]), max(Date[Date]), day) +1 , datesmtd('Date'[Date]))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[danny0428]

Thanks!