Forum Discussion
Same Store Flag
- 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...
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...
- dbright8 years agoRegular Visitor
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!