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

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

      • kaka's avatar
        kaka
        Helper II
        9 years ago

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