Forum Discussion
Helper ICalculate data at max startdate based on slicers
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);
- Quantity of each group at the maximum start date available in table A
- Quantity at the maximum start date available in table A and the maximum date.
I used this DAX measure:
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!
7 Replies
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
XuanHauHi Anonymous
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).
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
- Idrissshatila
Super User
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
- 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,
