Forum Discussion
Last date dax
Hi experts,
I want to filter records based on the date filed dynamically , when the user will select any of the month, then I want to show the data accordingly.
Please help
Date Type Value
20-06-2018 A 20
20-06-2018 B 40
19-06-2018 A 21
19-06-2018 B 39
Output on Visual:
Date Type Value
20-06-2018 A 20
20-06-2018 B 40
HI Anonymous,
You can create a measure formula to get the last date based on group and compare with current row date to return flag, then you can use this on ‘visual level filter’ to filter records:
formula = VAR _lastdate = CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table ), VALUES ( Table[Type] ) ) VAR currdate = MAX ( Table[Date] ) RETURN IF ( currdate = _lastdate, "Y", "N" )Regards,
Xiaoxin Sheng
2 Replies
HI Anonymous,
You can create a measure formula to get the last date based on group and compare with current row date to return flag, then you can use this on ‘visual level filter’ to filter records:
formula = VAR _lastdate = CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table ), VALUES ( Table[Type] ) ) VAR currdate = MAX ( Table[Date] ) RETURN IF ( currdate = _lastdate, "Y", "N" )Regards,
Xiaoxin Sheng
Hi,
Create a Calendar Table with a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. Drag Type to the Table/matrix visual. Write these measures
Last date = max(Data[Date])
Amount = calculate(sum(Data[Sales]),datesbetween(Calendar[Date],[last date],[last date]))
Hope this helps.
