Getting column value based on latest dates

[kaka]

I have a table with two columns of dates and associated sales values for different stores. So, a store can have multiple  date values  with sales values associated with them. 

Store#             Date1                  Date2                     SalesValue 

1                     02/21/2017         02/21/2017               22

1                     02/18/2017         02/18/2017               13 

2                    02/19/2017         02/19/2017                40 

2                     02/21/2017        02/21/2017                22

I would like to get the rows where the latest date1 is >= latest date2 and sales value = 22. 

So, I should end up with: 

1                     02/21/2017         02/21/2017               22

2                     02/21/2017        02/21/2017                22

I tried to do the following but it did not work: 

IF(MAXA(Date1) >= MAXA(Date2) && Sales = 22, "You got this", "You didnt) 

I am getting an error which says: A snigle value column Sales in table "Sales" cannot be determined. This can happen when a measure or formula refers to column that contains many values without specifying an aggregation such as min,max, etc. 

If I put a MAX around Sales, then it's grabbing the max values instead of 22.  How can I get those two columns? 

[SqlJason · Accepted solution]

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"
)

[Phil_Seamark]

This table gives you the results you posted, but I'm not 100% sure I fully understand.

Some more data might be helpful to understand the issue.

New Sales Table = FILTER(
 Sales,
 'Sales'[SalesValue]=22 
 && 'Sales'[Date1] >= 'Sales'[Date2]
 && 'Sales'[Date1] = MAX('Sales'[Date1])
 )

[kaka]

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 

[SqlJason · Accepted solution]

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"
)

[kaka]

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". 

[v-ljerr-msft · Accepted solution]

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.

Regards

[SqlJason]

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.

[SqlJason]

I created a calculated column like shown below

Test = IF(Sales[Date1]>=CALCULATE(MAX(Sales[Date2]), ALLEXCEPT(Sales, Sales[Store#])), [SalesValue])

Is this what you wanted? I also added a third store number just to check data

Edit

Forgot to add the SalesValue = 22 part

Test = IF(Sales[Date1]>=CALCULATE(MAX(Sales[Date2]), ALLEXCEPT(Sales, Sales[Store#])) && [SalesValue]=22, [SalesValue])

[kaka]

Can you please explain hwhat the formula says?