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

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?

MikeDubya's avatar
MikeDubya
Helper I
3 years ago

For more context, I am part of a group of people that fly and we want to see if power bi can calculate our currency. So if the dates are when I or someone else flew and the 0, 1, 2, or more is say how many times we took off, our currency is calculated from the third to last take off in this case. Sometimes we fly more than once a day so that needs to be calculated into the result.