Forum Discussion

XuanHau's avatar
XuanHau
Helper I
2 years ago

Calculate 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-groupl...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Thejeswar's avatar
Thejeswar
Super User
2 years ago

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's avatar
    XuanHau
    Helper I
    2 years ago

    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.