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:

 

What I need to do is starting from the most recent date where there is a 1 (or higher) find the third to last date where there is also a 1 (or higher). In Example A, since the most recent date is 2/9/23 it should return 1/25/2023. In Example B, the most recent date is 2/4/2023 then it would be 1/17/2023. So the result should look like this:

 

 

 

On top of that, I need to advance that date forward by 90 days so the end result would be 4/25/2023 and 4/17/2023, respectively. 
Eventually I will only show the final date result in my visual and I'm sure I can figure that out, I just can't seem to get any DAX to work to give me the result I need. I have tried TOPN, RANKX, Among others and I would imagine that my inexperience, I have only used DAX a handful of times, is the reason I am not getting this correct. 

TIA

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

8 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hu MikeDubya 

    try to plot a measure 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
    EXCEPT(_top3, _top2)
    • 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)