Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
slava_aptown
Frequent Visitor

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

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.

 

monthcustomer   category sales   sales_year_ago   number_of_stores
2023-10-01   cust1category1  100905
2023-09-01cust1category11201003
2023-08-01cust1category11501107

 

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
cust1category1   3702005
1 ACCEPTED SOLUTION
_elbpower
Resolver III
Resolver III

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.

 

View solution in original post

1 REPLY 1
_elbpower
Resolver III
Resolver III

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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.