Trying to calculate Same Store Sales based on period criteria

[Lluís]

The measure definition is described in https://en.wikipedia.org/wiki/Same-store_sales (Method 2)

My data model has typical tables: Dates, Sales & Stores

My solution build two primary measures : SSSales YTD & SSSales PY. And then I build a secondary measure SSSales YOY %

For example the YTD one is:

MEASURE 'Stores'[SSSales YTD] = IF(HASONEVALUE('Dates'[Year]), CALCULATE(SUM('Sales'[Amount]),
                        DATESYTD('Dates'[Date]),
                        YEAR('Stores'[OpenDate])<>VALUES('Dates'[Year]),
                        FILTER (Sales, 'Sales'[Date] >= IF (YEAR(RELATED('Stores'[OpenDate]))=VALUES('Dates'[Year])-1,  
                                            DATE(VALUES('Dates'[Year]), MONTH(RELATED('Stores'[OpenDate])), DAY(RELATED('Stores'[OpenDate]))),
                                            MIN('Dates'[Date]) ))  ))

The measure is computing the results OK, but is not a cumulative measure

Date                SSSales YTD       Desired results
20140101                       96                         96
20140102                 77.755                  77.851
20140103               100.764                178.615
20140104                 99.436                278.051

The issue is on Filter sales table, but I don't know how to solve ¿...?

Any ideas ?

Thanks in advance

Lluís

[v-haibl-msft]

@Lluís

In your desired results, why 20140102 has the result of 77.851 but not 173.755?

If you want the cumulative measure, you can try to create a new measure like below, which will reference the result of SSSales YTD measure.

Cumulative Measure = 
CALCULATE (
    [SSSales YTD],
    FILTER (
        ALLSELECTED ( Table1[Date] ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
    )
)

Best Regards,

Herbert