Max Value from the all column

[hyugo_v]

Hello everybody,

i'm facing a problem for a column i need to calculate :
That column is based on condition from another column, i will explain.
I need to make a column called "LTM-Status", that column need to filter as follow :
- all the Dates that are between my last Date from my column Date AND my last Date-12 months is called "LTM".
- all the Dates that are between my last Date-12 months AND my last Date-24months is called "LTM-1".

-Othewise it's called "N/A".

I tried it while using the MAX() function but it gives only the MAX value for each row and not the max value for all the column Date which is why i always end up with LTM filter because it goes line by line.

If anyone got an idea on how to do it that will save my life ^^
If it needs further explanation, i'll explain gladly.

Regards,

     

[AlB · Accepted solution]

@hyugo_v 

I'm not sure how you've tried but it should work. See it all at play in the attached file, where the latest (max) date in the column is 31/12/2020

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@hyugo_v 

You're quite right. It doesn't work well. Try this improved version:

LTM status V2 = 
VAR max_ = MAX ( Table1[Date] )
VAR date1_ = EDATE(max_,-12)
VAR date2_ = EDATE(max_,-24)
RETURN
SWITCH ( TRUE(), Table1[Date] > date1_, "LTM", Table1[Date] > date2_, "LTM-1", "N/A" )

You might want to change the inequality depending on where you want to include the edge dates

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[hyugo_v]

@AlB It works nicely ! Thank you a lot ! 

[AlB · Accepted solution]

@hyugo_v 

I'm not sure how you've tried but it should work. See it all at play in the attached file, where the latest (max) date in the column is 31/12/2020

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[hyugo_v]

it works indeed for the last date but when i can change the last Date for example to 21/10/2020 then the LTM will go from 01/01/2020 to last Date and not from 21/10/2020 to 21/10/2019. But still, thank you for your help !

[hyugo_v]

@AlB it works indeed, thank you a lot !! 

[AlB]

Hi @hyugo_v 

Try this for a calculated column in your table:

New column = 
VAR max_ =
    MAX ( Table1[Date] )
VAR diff_ =
    DATEDIFF ( Table1[Date], max_,  YEAR )
RETURN
    SWITCH ( diff_, 0, "LTM", 1, "LTM-1" )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[hyugo_v]

HI @AlB, 

thank you for the quick answer,

i tried to use your way and the problem was half solved, the filter "LTM" have to give me all the Dates between my LastDate and my LastDate- 12Months, here it gives me only the last Date for the filter "LTM"