Forum Discussion

dbright's avatar
dbright
Regular Visitor
8 years ago
Solved

Same Store Flag

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 ...
  • Seward12533's avatar
    8 years ago

    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...