Dax formula: how to use the last purchase cost prior to the filter date

[AndreaCBS]

Hello everyone, I have to write a DAX formula that must takes into consideration the last purchase cost prior to the filter date of the period for which the margin is being calculated.

So the idea is that I could select a date to filter my report, next it should takes the last purchase cost prior the filter date, and then (with this cost) calculate the margin.

[xifeng_L · Accepted solution]

Hi @AndreaCBS 

You can try this measure.

Measure = CALCULATE(SUM('Fact'[Cost]),LASTDATE('Fact'[Date]<MAX('Fact'[Date])))

If the date column used in slicer is from calendar table, then also need to remove the date filter.

Measure = CALCULATE(SUM('Fact'[Cost]),LASTDATE('Fact'[Date]<MAX('Calendar'[Date])),ALL('Calendar'))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~