Same Store Flag

[dbright]

I need to be able to shows months worth of data assuming a store was live at least 12 months before the reported month. THis means there will be a different abount of stores in my numbers each month as stores reach that 12 month requirement and excluded prior to that. Any help would be appreciated. I am able to do based on report date by doing Today()-365, but that doesnt work for a multi month analysis. For example:

March 2018 - $40,000 (based on 123 stores that were at least live as of March 1st 2017))

April 2018 - $55,000 (based on 128 stores that were at least live as of April 1st 2017)

I then want to look back to 2017 and get those same stores sales for comparison:

March 2017 - $35,000 (based on stores that were at least live as of March 1st 2017 and still live March 1st 2018)

Maybe I'm asking for too much...thoughts?

[Seward12533 · Accepted solution]

There are a lot of ways to do this. One solution that would be fairly easy to implement if you new to DAX/PowerBI would be to add a calcualted column in your fact table that compare the date in the row to the earliest date reported using DATEDIFF function. Unless of course you have a lookup table of Store opening dates.  There are some single formula dax and other solutions counting number of distinct months in the date table etc but see if this helps you out.  

You need a Measure to calcluate the earlierst sales date

Earliest Sales Date = First Sales Date = CALCULATE(MIN(FactTable[TransactionDate]),All(DateTable))

Then a Calcualted column in your fact table

Number of Months Open COLUMN = DATEDIFF([TransactionDate],[Earliest Sales Date],Month)

You can then write a measure for 

Sales Stores Open over 1YR = CALCULATE([Sales],Filter(FactTable,FactTable[Number of Months Open]>12)

You could then write similar measures for Count of Stores over 1YR etc...

[dbright]

I am somewhat new to the BI world and DAX, but this makes sense. I will work through this today. Thank you for the quick response!