Calculate data at max startdate based on slicers

[XuanHau]

Hi,

I need help with my report. I have:

• A table named A (fact) with 3 columns: group, Start date, quantity.
• A Datetable and a Grouplist table.
• Relationships: Startdate-datetable, group-grouplist.

I want to display 2 tables in the report with the slicers Monthyear slicer (from the Datetable), Group slicer (from the grouplist);

1. Quantity of each group at the maximum start date available in table A
2. Quantity at the maximum start date available in table A and the maximum date. 

I used this DAX measure:

CALCULATE(SUM(A[Quantity]), FILTER(Datetable, Datetable[Date] = MAX(A[Start Date])))

The totals in the two tables are correct, but the details for each row in the table are not correct. It should show data only for Max start date Feb 8, but it didn't

I can't upload the pbix file, so i share the link here.

Can someone help me with this? Thanks in advance!

[v-mengmli-msft]

Hi @XuanHau ,

How do you create the relationship? Could you please show more details about your question?

I recommend you try to add all in your current measure.

CALCULATE(SUM(A[Quantity]), FILTER(ALL(Datetable), Datetable[Date] = MAX(A[Start Date])))

If above method doesn’t work, here is another workaround for your reference.

Measure = IF( MAX('A'[Start Date]) = MAXX( FILTER(ALL('A' ) , YEAR( 'A'[Start Date] ) = YEAR(MAX('Datetable'[Date]) ) &&  MONTH( 'A'[Start Date] ) = MONTH( MAX('Datetable'[Date]) ) ) , [Start Date]) ,1,0)

Then set the measure in Filter as bellow.

Here is result of my test.

My table A.

When I select August 2024, the table just shows one record with max StartDate.

Best regards,

Mengmeng Li

[XuanHau]

Hi @v-mengmli-msft 

The workaround measure works for me, but I have a question: can we include it in a measure instead of using a filter? I also use quantity at the max date in another measure.

I have attached the link to my report in the original post for my example data (this file does not use the workaround you mentioned).

[v-mengmli-msft]

Hi @XuanHau ,

I'm afraid can't. The measure just returns a number, it can't be used as Filter in column field. In general, we recommend that user put the measure in Filter and complete the row filter based on the value returned by the measure.😀

Best regards,

Mengmeng Li

[Thejeswar]

Hi @XuanHau ,

I think whatever is the DAX that you share to get the maximum date quantity is correct. I tried with some dummy data and I was able to get the right numbers with the same DAX query. Find below

Max Date Quantity = CALCULATE(SUM(A[QUANTITY]), FILTER(Datetable, Datetable[Date] = MAX(A[Start Date])))

For the data with max quantity in each group, you need to have a separate measure

Date with Highest Value = 
VAR MAX_QUANTITY = CALCULATE(MAX(A[QUANTITY]), GROUPBY(GroupList, GroupList[Group]))
RETURN
CALCULATE(MAX(A[Start Date]), A[QUANTITY] = MAX_QUANTITY, USERELATIONSHIP(A[Group], GroupList[Group]))

The Tables are connected as 

• Relationships: Startdate-datetable, group-grouplist.

Source has data only till 30th August 2024 with only one group per day as shown below

Source showing the days with Max Data in each group

DAX output

Please mark this as solution, if this solves your need! Appreciate a Kudo!

Regards,

[XuanHau]

hi @Thejeswar it seems we have a misunderstanding here. I need the quantity for the max date, not the max quantity in each group
I have already added the link to my PBIX file as an example. I'm not sure if there is something wrong with my DAX measure.

[Idrissshatila]

Hello @XuanHau ,

are you sure there's a relationship in the data model between the date table and the table with data on the date column ?

[XuanHau]

Hi @Idrissshatila i've checked the relationships multiple times, it still works. So i think the issue comes from my dax