Forum Discussion

kaka's avatar
kaka
Helper II
9 years ago
Solved

Getting column value based on latest dates

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? 

Need Help
  • SqlJason's avatar
    SqlJason
    9 years ago

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

8 Replies

  • SqlJason's avatar
    SqlJason
    Memorable Member
    9 years ago

    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's avatar
      kaka
      Helper II
      9 years ago

      Can you please explain hwhat the formula says? 

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      kaka
      Helper II
      9 years ago

      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's avatar
        SqlJason
        Memorable Member
        9 years ago

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