Calculate MAX Date based year context

[finno14]

Hello, 

I need some help embedding the Financial year context into a DAX measure:

The below measure understandably brings back P13 21/22 as im asking for the MAX Number, im unsure as to how to add in year/financial year context as the correct Max_period should be P04 22/23?

Max_Period = 


var Max_Period = calculate(MAX('Alex Date Period Flow'[Period - Numbered]), ALLSELECTED('Alex Date Period Flow'))

return Max_Period

Any assistance would be greatly appreciated.

Thank you!

Alex

[johnt75]

You can use TOPN, something like

Max Period = SELECTCOLUMNS( TOPN(1, 'Table', 'Table'[Fin Year Period]), 'Table'[Period number])

You may need to change the [Fin Year Period] column to instead be whichever column is providing the Sort Order for that column

[finno14]

Hi @johnt75 ,

Thank you for taking the time to respond, greatly appreciated!

I'm struggling to get the formula to work, below is my current date table for context. Does my Financial year need to be amended into a whole number? 

Kind regards,

[johnt75 · Accepted solution]

If the slicer you are using is also coming from the date table then I think the below should work

Max Period = SELECTCOLUMNS( TOPN(1, 'Date', 'Date'[Date]), 'Date'[Period])

[finno14]

Hi John, 

This formula was spot on, I was trying to display it in a card with a filter within it *Facepalm*

Thank you for your help! Lifesaver!!!

Alex

[finno14]

Good Morning John,

Apologies, I do not mean to be frustrating however I cannot seem to make the code viable... I have attached below so you can see where im going wrong. Apologies again havent too much experience working with TOPN(

Max Period = 


  SELECTCOLUMNS(
TOPN(1,'Alex Date Period Flow','Alex Date Period Flow'[Date])
," Select Column requires a name after ( ", 'Alex Date Period Flow'[Period])

[johnt75]

That looks OK to me. What results are you seeing? Is it possible to share a sample PBIX ?