How to write this measure in DAX?

[Penn]

Hi all,

I have a table [Opportunity History] that looks like this and I have independent date table 'Calendar'. How do I create a measure that return the last stage at each month end (with a month slicer). 

The desire result will be

1. If I select Feb 2017 in the slicer, return

2. If I select Dec 2017 in the slicer, return Quotation

3. If I select Mar 2018 in the slicer, return Lost - Abandoned

The measure that I wrote doesn't work

Stage EOM = CALCULATE(MAX([Stage]), FILTER('Opportunity History', 'Opportunity History'[Date Time] <= MAX('Calendar'[This Month End])))

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

Stage EOM = CALCULATE(MAX([Stage]), FILTER(all('Opportunity History'), 'Opportunity History'[Date Time] <= MAX('Calendar'[This Month End])))

Try All. Also I think it should be = not <=

[Anonymous]

@Penn Following might help you

https://community.powerbi.com/t5/Desktop/Dax-Top-1-Top-2-with-filter-selection/td-p/384302