Like for like comparison (SUMX doesn`t work in IF condition)

[Renata92]

Hi everybody, 

I just need help for my L4L comparison! 

I need to calculate the sales comparison only for the stores, which was open in the same period last year since 6 months. If the criteria is fulfilled it is 1, otherwise 0 in the sales table.

The logic is - if the reported_sales_lfl CY + reported_sales_lfl PY = 2 (both of them can be considered) then I can calculate the sales of this period.

My formula: 

Logik Nettoumsatz gemeldet L4L =

VAR L4L_Current_Year = SUM(fct_reported_sales[reported_sales_lfl])

VAR L4L_Past_Year = CALCULATE(SUM(fct_reported_sales[reported_sales_lfl]), SAMEPERIODLASTYEAR('Date'[Date]))

VAR L4L_Total = L4L_Current_Year + L4L_Past_Year

RETURN

IF(L4L_Total >= 2,, [Reported Sales])

The only problem is that the total of the columns is false, so I did a second measure to fix this problem 

Nettoumsatz gemeldet L4L =

    SUMX(

        SUMMARIZE(dim_sales_lfl,'Date'[Date],dim_locations[location_id]),

            CALCULATE([Logik Nettoumsatz gemeldet L4L])

            )

But if I bring the 2 measures together i get the same result as at Logik Nettoumsatz gemeldet L4L -  do you have an idea how I could bring the 2 measures in 1? Thank you!

[v-kkf-msft · Accepted solution]

Hi @Renata92 ,

Please try this formula to see if it returns the correct value. If not, please share your example data (with sensitive data removed).

Measure =
VAR L4L_Current_Year = SUM(fct_reported_sales[reported_sales_lfl])
VAR L4L_Past_Year = CALCULATE(SUM(fct_reported_sales[reported_sales_lfl]), SAMEPERIODLASTYEAR('Date'[Date]))
VAR L4L_Total = L4L_Current_Year + L4L_Past_Year
RETURN
SUMX(
      FILTER( SUMMARIZE(dim_sales_lfl,'Date'[Date],dim_locations[location_id]), L4L_Total < 2 ),
      CALCULATE([Reported Sales])
    )

Best Regards,
Winniz

[Renata92]

Thank you for your answer! The measure doesn't show the correct value. How can I remove sensitive data?