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

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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
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