DAX to measure Same Store Sale

[Anonymous]

Hello everyone!

I am having trouble writing a DAX formula in Power BI to calculate Same Store Sales (SSS). My calculation of SSS is (CURRENT_VALUE_ / PREVIOUS_VALUE_) - 1.

The problem is that, when looking at the CLIENT_ID hierarchy in a matrix visual in Power BI, I want the SSS measure to consider only the CURRENT_VALUE_ of the brands that also had sales in PREVIOUS_VALUE_.

For example, in the figure below, I want CLIENT_ID 123456 to show me an SSS of 27% in total, instead of 100%. That is, I want it to consider (140 / 110) - 1, instead of (220 / 110) - 1, as it should only sum the CURRENT_VALUE_ of the brands that also have PREVIOUS_VALUE_ (that is, only the GREEN and PINK brands).

My current DAX formula is as follows:

VAR tempTable = SUMMARIZE(clients, clients[CLIENT_ID], "PER_ATUAL", [CURRENT_VALUE_] ,"PER_ANTER", [PREVIOUS_VALUE_])
VAR currentValue = SUMX(FILTER(tempTable, NOT ISBLANK([PER_ATUAL]) && NOT ISBLANK([PER_ANTER])), [PER_ATUAL])
VAR previousValue = SUMX(FILTER(tempTable, NOT ISBLANK([PER_ATUAL]) && NOT ISBLANK([PER_ANTER])), [PER_ANTER])

RETURN
IF(DIVIDE(currentValue, previousValue, BLANK()) -1 = -1 ,
BLANK(),
DIVIDE(currentValue, previousValue, BLANK()) -1
)

Any help or tips?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how you semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Current sales: = 
SUM( sales[sales] )

Previous sales: = 
CALCULATE (
    [Current sales:],
    OFFSET ( -1, ALL ( 'year'[year] ), ORDERBY ( 'year'[year], ASC ) )
)

Expected result measure: = 
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( sales, client[client_id], store[store_id], 'year'[year] ),
            "@current", [Current sales:],
            "@prev", [Previous sales:]
        ),
        NOT ISBLANK ( [@prev] )
    )
VAR _result =
    DIVIDE ( SUMX ( _t, [@current] ), SUMX ( _t, [@prev] ) ) - 1
RETURN
    IF ( NOT ISBLANK ( [Previous sales:] ), _result )

[Anonymous]

It worked perfectly. Thank you so much!!