DAX measure evaluated globally and based on date

[alexschindler]

Hi all

I am trying to solve the following problem:

I need a measure ("like_for_like") that shows whether a store (2; see screenshot) was open during the whole time selected by a date slicer (1). It should return "1" if the store was open the whole time and "0" otherwise.

In my example all of 2024 is selected. The store "store_1" was open the whole year, so it should get a "1", while the store "store_2" closed at the end of January 2024, so it should get a "0".

My measure works fine on level store (3). However, if I add month to the table, the measure is evaluated by month (4), and "store_2" gets a "1" in January since it was still open. This is not what I need.

I would like to have a measure that is

(a) only evaluated globally, but that is

(b) still dependent on the global slicer (1).

So I would like the measure to return a "0" for "store_2" for all the months since the store was not open for all of 2024.

I have tried many different formulae, none of which were successful. For some reason I only achieved either (a) or (b). I tried different approaches including functions such as ALL, ALLEXCEPT, REMOVEFILTERS, ...

Does anyone have an idea for how to solve this? Thanks a lot for any thoughts!

Example of an approach (that did not work out): temporary table with all the stores that are open during the selected period.

Problem: works globally, but is evaluated per month.

like_for_like =

VAR stores_open = SELECTCOLUMNS(

    FILTER('DIM_STORE', 'DIM_STORE'[opening_date] <= MIN(DIM_DATE[date]) && 'DIM_STORE'[closing_date] >= MAX(DIM_DATE[date])), "store_id_open", 'DIM_STORE'[store_id])

RETURN    IF(CALCULATE(COUNTROWS(DIM_STORE), DIM_STORE[store_id] IN stores_open), 1, 0)

 

[Alican_C · Accepted solution]

Hi,

You can use a combination of DAX functions to create a measure that checks the store's open and close dates against the entire date range selected.

like_for_like =
VAR MinDateSelected = MINX(ALLSELECTED(DIM_DATE), DIM_DATE[date])
VAR MaxDateSelected = MAXX(ALLSELECTED(DIM_DATE), DIM_DATE[date])
RETURN
IF(
COUNTROWS(
FILTER(
DIM_STORE,
DIM_STORE[opening_date] <= MinDateSelected &&
(ISBLANK(DIM_STORE[closing_date]) || DIM_STORE[closing_date] >= MaxDateSelected)
)
) = COUNTROWS(DIM_STORE),
1,
0
)

[alexschindler]

Hi Alican_C

Woah, that's amazing, works like a charm!

Thanks so much - you totally made my day! Really appreciate it!

Alex

[alexschindler]

@Alican_C 

Sorry for bothering you one more time, but actually I realized that the solution does not work yet for SAMEPERIODLASTYEAR.

In my use case users would select a time period in the current year, say, Jan 1, 2024 - March 31, 2024. They would then be shown the net sales for the 2024 period, but also for the corresponding 2023 period (Jan 1, 2023 - March 31, 2023).

So the like_for_like measure should be 1 if the store was open Jan 1, 2023 - March 31, 2024.

I made some small modifications to extend the period that is checked to the previous year. I have 2 parameters "_last_year_manual" and "_current_year_manual".

VAR MinDateSelectedPrecalculation = MINX(ALLSELECTED(DIM_DATE), DIM_DATE[date])

VAR MinDateSelected = IF(YEAR(MinDateSelectedPrecalculation) = [_current_year_manual], MINX(ALLSELECTED(DIM_DATE), DATEADD( DIM_DATE[date], -1, YEAR)), MinDateSelectedPrecalculation)


VAR MaxDateSelectedPrecalculation = MAXX(ALLSELECTED(DIM_DATE), DIM_DATE[date])

VAR MaxDateSelected = IF(YEAR(MaxDateSelectedPrecalculation) = [_last_year_manual], MAXX(ALLSELECTED(DIM_DATE), DATEADD( DIM_DATE[date], 1, YEAR)), MaxDateSelectedPrecalculation)

However, if I now run a SAMEPERIODLASTYEAR on my measures, it evaluates the MinDateSelected and MaxDateSelected on month level again rather than globally.

See screenshot: For the (A) measures it works great. For the (B) measures with SAMEPERIODLASTYEAR it evaluates by month again.

Do you have an idea how to fix this such that it also works with SAMEPERIODLASTYEAR?

Many thanks.

[alexschindler]

Hi @Alican_C 
I have been doing more research and think I found out that the problem does not lie with the SAMEPERIODLASTYEAR, but rather with the CALCULATE. I think it is a problem of context.

I am now using the like_for_like measure for the net sales:

net_sales = SWITCH(
    TRUE(),
        [like_for_like_selected] = "All in", SUM('FACT_SALES'[net_sales]),
        [like_for_like_selected] = "L4L", CALCULATE(SUM('FACT_SALES'[net_sales]), FILTER('DIM_STORE', DIM_STORE[_like_for_like] = 1) ),
        [like_for_like_selected] = "Non-L4L", CALCULATE(SUM('FACT_SALES'[net_sales]), FILTER('DIM_STORE', DIM_STORE[_like_for_like] = 0) )
)

Those work great, but then I have a lot of measures that go something like:

net_sales_last_year = CALCULATE([net_sales], SAMEPERIODLASTYEAR('DIM_DATE'[date] ) )

And the CALCULATE messes up the like_for_like measure since it adds date context to the table again: then the like_for_like measure is again evaluated by month.

Do you think this is correct? And if yes, how can it be avoided?

Many thanks for your time!

Alex

[alexschindler]

After more reading and researching I figured out that I can get the like_for_like measure to work if I add another ALLSELECTED:

net_sales_last_year = CALCULATE([net_sales], SAMEPERIODLASTYEAR(ALLSELECTED('DIM_DATE'[date] ) ))

This fixes the like_for_like problem. However, the problem is then that it no longer shows the net sales by month, but the same value for each month.

I think I would need to make sure the ALLSELECTED is only fed to the variables of the like_for_like measures, but not to the part with the net sales.