topic Dax formula: how to use the last purchase cost prior to the filter date in DAX Commands and Tips

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

Anonymous — Wed, 05 Jun 2024 06:58:09 GMT

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.

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

xifeng_L — Wed, 05 Jun 2024 07:37:04 GMT

Hi Anonymous

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~