Forum Discussion

MikeDubya's avatar
MikeDubya
Helper I
3 years ago
Solved

Getting a specific date value based on another columns value

Hello all, First time poster, long time reader. I have a set of data that I need to get a specific date out of and I am just not getting what I need. Here is a basic snippit of the data:   ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi MikeDubya 

    Sorry i misconsidered that last point. try like:
    Measure =
    VAR _table=
    CALCULATETABLE(
            VALUES(TableName[Col1]),
            TableName[Col2]=1
    )
    VAR _top3 =
    TOPN(3, _table, TableName[Col1] )
    VAR _top2=
    TOPN(2, _table, TableName[Col1] )
    RETURN
    EDATE(EXCEPT(_top3, _top2), 3)
MikeDubya's avatar
MikeDubya
Helper I
3 years ago

This works great! The only thing left is to add 90 days to the result that the above gives. I set the "EXCEPT(_top3, _top2)" to a variable called final ( which worked fine if I just put final after RETURN) and then tried DATEADD(final, 90, DAY) but it just gave me a blank result. Thoughts? Should I add the 90 to another variable? 

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi MikeDubya 

Sorry i misconsidered that last point. try like:
Measure =
VAR _table=
CALCULATETABLE(
        VALUES(TableName[Col1]),
        TableName[Col2]=1
)
VAR _top3 =
TOPN(3, _table, TableName[Col1] )
VAR _top2=
TOPN(2, _table, TableName[Col1] )
RETURN
EDATE(EXCEPT(_top3, _top2), 3)
  • MikeDubya's avatar
    MikeDubya
    Helper I
    3 years ago

    That's it! Thank you so much!

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      this works mainly because one-row-one-column table can be treated as value in DAX directly.

      • MikeDubya's avatar
        MikeDubya
        Helper I
        3 years ago

        I did have one more question. There is a bit of a change to what I need. In example B, you'll notice that instead of a 1 there is a 2 on one of the dates. This will happen often in the real data. I didn't know this when I originally posted but the total of the column should count/add to 3 and whereever the third is counted that is the date we want. So for example B, the date that should be returned is 1/25/2023 not 1/17/2023 because the second column, from most recent date to older, would add up to 3 there. The first example is still correct. Any thoughts? I dinked around with the code you have but not sure where to have it count that. I'd imagine an IF statement would handle this but curious what you think. To make it clear, once the total of the second column is 3 or greater, what is the date where it hits 3?