Same Store YTD Calculation

[nickvb]

I am hoping for some assistance in creating a YTD same store growth calculation.  The goal is to see how stores are growing that were open in both the prior and current year. 

The dax calculation I have right now is below. This works great for individual periods (we measure in weeks on a 4-4-5 calendar) as it looks to see if there is revenue in the current and prior year period and if so includes it in the calculation to determine growth.

Same Store Prior Year Revenue = CALCULATE([Prior Year Revenue],FILTER(customers,[Current Year Revenue] > 0 && [Prior Year Revenue] >0))

Same Store Revenue = CALCULATE([Current Year Revenue],FILTER(customers,[Current Year Revenue] > 0 && [Prior Year Revenue] >0))

Same Store Calc = ([Same Store Revenue] / [Same Store Prior Year Revenue]) - 1

The difficulty I am having is that for a YTD calculation is it gets skewed if a store opens in the middle of the current or prior YTD period. For instance, if I'm looking at weeks 1-26 of a year and in the prior year a new store opens in week 13 then it is going to count that store in the YTD calculation.  However, since there was no revenue in the weeks 1-12 of the prior period it's going to output an incorrectly high growth number.  

Does anybody know how to correct for this? Effectively I'm wondering how to modify the calculation so that it looks at each week for each store individually rather than the entire time period. 

Thanks in advance

[Anonymous]

Hi @nickvb ,

here because there is no data given i'm using 'table' and some column names. Please replace them with the actuals ones.

Please use the below formula :

SAME PERIOD LAST YEAR  = 
VAR DataMaxDate =
    CALCULATE ( MAX ( 'Table'[Date.Date].[Date] ), ALL ('Table' ) )
RETURN
    CALCULATE (
        [YTD Sales],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ('Table'[Date.Date].[Date] ),
                DATESBETWEEN ( 'Table'[Date.Date], BLANK (), DataMaxDate )
            )
        )
    )

NOTE : make sure to change Date column to date type otherwise you will end up with the same issue you are facing.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

Regards,

Gaurav Raj Singh

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/