Forum Discussion
Calculate max date
Hello please I need your help on this, I need to calculate the max date grouped by another column based on a date filter, I have tried this measure but did not work
MASDD = var minDate=min('Date'[Date]) var maxDate =max('Date'[Date])
return calculate(max('Table'[vdate]),ALLEXCEPT('Table','Table'[pid]),ALLEXCEPT('Date','Date'[Date])) return calculate(c, FILTER('Table','Table'[Mydate]<=maxDate && 'Table'[Mydate]>= minDate))
For example, as you can see here
the MAXDD for p1 based on the date filter should always be 2021-01-03.
And Please how can I achieve this :
4 Replies
toum , Try measures like
Last = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[ID] = max(Data1[ID]) ),Data1[MyDate])
return
CALCULATE([Measure], filter( (Data1), Data1[ID] = max(Data1[ID]) && Data1[MyDate] =_max))Sum Last = sumx(VALUES(Data1[ID]) , [Measure])
I tried this, but the problem, is that the total and subtotal in the matrix are wrong, what should I do please?
Hi toum ,
Here are the steps you can follow：
1. Create measure.
Measure = var _mindate= MINX(ALLSELECTED('Date'),[Date]) var _maxdate= MAXX(ALLSELECTED('Date'),[Date]) return MAXX( FILTER(ALL('Table'), 'Table'[pid]=MAX('Table'[pid])&&'Table'[Mydate]>=_mindate&&'Table'[Mydate]<=_maxdate),[Mydate])2. Result:
If you appear that the total is incorrect, You can create a Measure, use HASONEVALUE and IF to make judgments, and make sure Total is correct.
Refer to:Measure Total - Microsoft Power BI Community
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you a lot, but another thing please in the visual I want to display only the rows where the mydate is equal to the measue "max date in range for each pid(ID)" and the sum(qty) with the information related to that ID for that "measure date"
