Forum Discussion
Max Value from the all column
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,
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
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
7 Replies
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
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
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
