DAX

[Luck]

I need a measure to return me the last date of a sale to a client on the last month of a reference date. So, if i've selected mar/23, i need a measure to return the last date of a sale in feb/23.

I was trying to use calculate Max[datesales column] and filtering by datesales column so I could use a timeline. But I cant work out how to make this work.

I need something that i can also use in power pivot.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

expected result measure: =
VAR _selectedmonthend =
    MAX ( 'Calendar'[Month-Year sort] )
RETURN
    MAXX (
        CALCULATETABLE (
            Sales,
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year sort] < _selectedmonthend )
        ),
        Sales[Date]
    )