Forum Discussion
Getting column value based on latest dates
Based on the 3 conditions you sid, the calculated column is given below.
If you don't require any condition, you can just remove the && part for that condition
Test =
IF (
Sales[Date1] --Current row of Date1
>= CALCULATE ( MAX ( Sales[Date2] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date2 for a store
&& Sales[Date1]
>= CALCULATE ( MAX ( Sales[Date1] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date1 for a store
&& [SalesValue] = 22, -- sales value condition
"You got it",
"Failed"
)Hi kaka,
I just verified that the formula provided SqlJason should work in your scenario if it is used to create a calculate column.
If you need a measure to do calculation instead, then the formula below is for your reference.
Measure = IF ( MAX(Sales[Date1]) --Current row of Date1 >= CALCULATE ( MAX ( Sales[Date2] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date2 for a store && MAX(Sales[Date1]) >= CALCULATE ( MAX ( Sales[Date1] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date1 for a store && MAX([SalesValue]) = 22, -- sales value condition "You got it", "Failed" )Here is the sample pbix file for your reference.:smileyhappy:
Regards
I am trying to come up with a formula which adds the text "You got it" or " Failed" in the last column called Results.
So, the "Results" table sould be filled out based on the rule which says that if the latest Date1 >= latest Date2 of a particulater store
and the salesvalue = 22, then print "You got it" in the Results column.
Store# Date1 Date2 SalesValue Results
1 02/21/2017 02/21/2017 22 You Got it
1 02/18/2017 02/18/2017 13 Failed
2 02/19/2017 02/19/2017 40 Failed
2 02/21/2017 02/21/2017 22 You Got it
Based on the 3 conditions you sid, the calculated column is given below.
If you don't require any condition, you can just remove the && part for that condition
Test =
IF (
Sales[Date1] --Current row of Date1
>= CALCULATE ( MAX ( Sales[Date2] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date2 for a store
&& Sales[Date1]
>= CALCULATE ( MAX ( Sales[Date1] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date1 for a store
&& [SalesValue] = 22, -- sales value condition
"You got it",
"Failed"
)
I honestly dont understand what this does. However, I gave it a try and Sales[Date1], it's actually wanting that date to be with either MAX< MIN, or any other aggregate function. So, after i fixed that, then you end up with "Failed".
So what you are saying is that you get only "Failed" after using my formula. Is my understanding correct?
This has to be a calculated column and not a measure. If you show with an image what you did (along with the error message as well as the calculation), I might be able to help you correct the error.
Hi kaka,
I just verified that the formula provided SqlJason should work in your scenario if it is used to create a calculate column.
If you need a measure to do calculation instead, then the formula below is for your reference.
Measure = IF ( MAX(Sales[Date1]) --Current row of Date1 >= CALCULATE ( MAX ( Sales[Date2] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date2 for a store && MAX(Sales[Date1]) >= CALCULATE ( MAX ( Sales[Date1] ), ALLEXCEPT ( Sales, Sales[Store#] ) ) --latest Date1 for a store && MAX([SalesValue]) = 22, -- sales value condition "You got it", "Failed" )Here is the sample pbix file for your reference.:smileyhappy:
Regards