CurrentGroupBY error

[toum]

Spoiler

Hello all,
Please can someone help me with this, I've spent a week on this but my code does not work:

'''

N = SUMX(GROUPBY(FILTER(tab, tab[VDate] <= MAX('Date_Table'[date])), tab[pid], "max_date", MAX(tab[VDate])), SUMX(FILTER(tab, tab[pid] = CURRENTGROUP()[pid] && tab[VDate] = CURRENTGROUP()["max_date"]), tab[qty]))

'''

the ["max_date"] and [pid] are in red Unexpected expression

1. First, filter the table to only include rows where the date column is less than or equal to the maximum date selected in the slicer. This can be done using the FILTER function and the MIN and MAX functions from the slicer table.
2. Next, group the filtered table by the "name" column using the GROUPBY function, and create a new column "max_date" that holds the maximum date for each group.
3. Then, use the SUMX function to sum the QTY column for each group where the date column of that row is equal to the max_date of that group

[v-jingzhang · Accepted solution]

Hi @toum 

If I understand it correctly and if your final expected result is a new table, you can try below DAX to create a new table. 

New Table = 
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
return
ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))

If your expected result is a measure, you can try 

Measure = 
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
var __table2 = ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))
return
SUMX(__table2,[total_qty])

I used SUMMARIZE instead of GroupBy. The sample file has been attached at bottom including above two examples. 

If this is not what you want, can you please provide some sample data with the necessary columns VDate, pid, qty in table format as well as the expected result based on the sample data? This could help us understand the problem and provide a possible solution faster. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @toum 

If I understand it correctly and if your final expected result is a new table, you can try below DAX to create a new table. 

New Table = 
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
return
ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))

If your expected result is a measure, you can try 

Measure = 
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
var __table2 = ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))
return
SUMX(__table2,[total_qty])

I used SUMMARIZE instead of GroupBy. The sample file has been attached at bottom including above two examples. 

If this is not what you want, can you please provide some sample data with the necessary columns VDate, pid, qty in table format as well as the expected result based on the sample data? This could help us understand the problem and provide a possible solution faster. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @toum Sorry I forgot to attach the sample file in previous reply. Please find it in this reply. 

[toum]

Thank you a lot for your help