Filtering CURRENTGROUP() in DAX to get the last value by date

[slava_aptown]

Hi!

I have a table on a granularity month-product category-customer.
Its relationship are irrelevant for the question, as calculations are limited by its columns.

month	customer	category	sales	sales_year_ago	number_of_stores
2023-10-01	cust1	category1	100	90	5
2023-09-01	cust1	category1	120	100	3
2023-08-01	cust1	category1	150	110	7

My ultimate goal is to sum sales column for rows in the table filtered by columns from the table itself or their derivatives, no interaction with other tables.

I achieve this by the following formula.
1. I do double GROUPBY as I need to calculate on customer-category level, but at the same time if several months are chosen in slicers, I want to sum them at customer-category level.
2. I use ADDCOLUMNS to calculate derivative columns.
3. Then I filter by new columns and sum sales for the filtered table.

Measure =

VAR _tbl1 =
GROUPBY(
   initial_table,
   initial_table[month],
   initial_table[customer],
   initial_table[category],
   "sales", SUMX(CURRENTGROUP(), initial_table[sales]),
   "sales_ya", SUMX(CURRENTGROUP(), initial_table[sales_year_ago]),
   "num_of_stores", MAXX(CURRENTGROUP(), initial_table[number_of_stores])
)

VAR _tbl2 =
ADDCOLUMNS(
   GROUPBY(
      _tbl1,
      [customer],
      [category],
      "SALES", SUMX(CURRENTGROUP(), [sales]),
      "SALES_YA", SUMX(CURRENTGROUP(), [sales_ya]),
      "NUM_OF_STORES", SUMX(CURRENTGROUP(), [num_of_stores])
   ),
"SALES INDEX YA", DIVIDE([SALES], [SALES_YA]) * 100
)
return

SUMX(
   FILTER(
      _tbl2,
      [SALES INDEX YA] < 100
   ),
   [SALES]
)

The problem is that for the column number_of_stores I want to get the value for the last month in _tbl2, not the sum or max. It could have been achieved by filtering CURRENTGROUP for the last month, but it can't be filtered.

In short, NUM_OF_STORES column in _tbl2 should be the value for the last month in CURRENTGROUP, and I do not know how to achieve this.

So the virtual _tbl2 for the example table should look like this given that I chose all 3 months in a slicer.

customer	category	SALES	SALES_YA	NUM_OF_STORES
cust1	category1	370	200	5

[_elbpower · Accepted solution]

To calculate the NUM_OF_STORES column in _tbl2 as the value for the last month in the CURRENTGROUP, you can modify your DAX code to achieve this. One way to do this is by using a combination of the FILTER and EARLIER functions to filter the initial_table for the last month within each group. Here's how you can do it:

Measure = 
VAR _tbl1 =
    GROUPBY(
        initial_table,
        initial_table[month],
        initial_table[customer],
        initial_table[category],
        "sales", SUMX(CURRENTGROUP(), initial_table[sales]),
        "sales_ya", SUMX(CURRENTGROUP(), initial_table[sales_year_ago]),
        "num_of_stores", MAXX(CURRENTGROUP(), initial_table[number_of_stores])
    )

VAR _tbl2 =
    ADDCOLUMNS(
        _tbl1,
        "SALES", [sales],
        "SALES_YA", [sales_ya],
        "NUM_OF_STORES", 
            SUMX(
                FILTER(
                    initial_table,
                    initial_table[customer] = EARLIER(initial_table[customer]) &&
                    initial_table[category] = EARLIER(initial_table[category]) &&
                    initial_table[month] = MAXX(CURRENTGROUP(), initial_table[month])
                ),
                initial_table[number_of_stores]
            ),
        "SALES INDEX YA", DIVIDE([SALES], [SALES_YA]) * 100
    )

RETURN
    SUMX(
        FILTER(
            _tbl2,
            [SALES INDEX YA] < 100
        ),
        [SALES]
    )

n the code above, I added a new column NUM_OF_STORES in _tbl2 that uses SUMX and FILTER to filter the initial_table for the last month within each group defined by customer and category. The EARLIER function allows you to reference the values of the current row in _tbl1 within the filter condition.