Last date dax

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous · Accepted solution]

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