DAX - Sales Comparison (Calculate - Userelationship - SUMX)

[sashaxiv]

Morning all,

I would like to compare sales between stores in the same situation (Feature X flag) in both years (2021 vs 2019). Something similar to next table:

I have a relationship between sales and dates. For each date I have a column that allows me to know sales in the equivalent day of 2020 (default) and 2019. 

1. Getting data in 2021 in stores with Feature X is easy: SUMX(sales * hasPropertyX)
2. Getting data in 2019 in stores with Feature X is straighforward as well: CALCULATE(SUMX(sales * hasPropertyX), USERELATIONSHIP(Sales[date], Dates[SameDate2019))

My question is, how can i get sales in 2021 in stores with Feature X in 2021 and 2019 (like for like stores)?

My measure in point "1" takes into account stores 1 and 2, so it is incorrect. Only stores 3 and 4 sould be taken into account.

Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi,

please check the below picture and the attached pbix file.

Sales with feature X : =

VAR currentyear =

MAX ( 'Calendar'[Year] )

VAR newtable =

VALUES ( Codes[Code] )

VAR add_featurecolumn =

SUMMARIZE (

FILTER (

ADDCOLUMNS (

newtable,

"@feature", CALCULATE ( AVERAGE ( Data[HasPropertyX] ), REMOVEFILTERS ( 'Calendar' ) )

),

[@feature] = 1

),

Codes[Code]

)

RETURN

IF (

HASONEVALUE ( 'Calendar'[Month & Year] ),

CALCULATE ( SUM ( Data[Sales] ), add_featurecolumn ),

DIVIDE (

CALCULATE ( SUM ( Data[Sales] ), add_featurecolumn, 'Calendar'[Year] = 2021 )

- CALCULATE ( SUM ( Data[Sales] ), add_featurecolumn, 'Calendar'[Year] = 2019 ),

CALCULATE ( SUM ( Data[Sales] ), add_featurecolumn, 'Calendar'[Year] = 2019 )

)

)