Forum Discussion

AD_SNOW's avatar
AD_SNOW
Icon for Helper I rankHelper I
2 years ago
Solved

Find the first latest date and second latest date based on quarter selection dynamically

Team, I have a date (which doesn't have continues dates) and quarter fields in my table (mentioned below). My quarter will go as slicer/filter and I want to find the first latest date and the second...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi AD_SNOW 
    To get the desired result you can apply an index of every date according to the quarter from PQ . 

    The guide of how to get this is here :

    https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

    (Row Number By Group)

    I also attached a pbix ,  you can follow my steps

    From the moment that you have an index this is easy, you can create a simple dax measure for the flag :

    Flag = if(max('Table'[Index]) in {1,2}, MAX('Table'[Index]), " " )

     

    as I mentioned Pbix is attached 🙂

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

KeyurPatel14's avatar
KeyurPatel14
Icon for Responsive Resident rankResponsive Resident
2 years ago
Hello AD_SNOW ,
Hope you are doing well.

Could you please try the below measure:
(Please know that, I have considered the QTR from the same table, if you have a date dimension table, kindly make required change in the measure)

Max QTR Date =
VAR maxdate =
CALCULATE(
    MAX('Table (2)'[Date (DD/MM/YY)]),
    ALLEXCEPT('Table (2)','Table (2)'[Quarter])
)
VAR curdate =
SELECTEDVALUE('Table (2)'[Date (DD/MM/YY)])
VAR secondmaxdate =
CALCULATE(
    MAX('Table (2)'[Date (DD/MM/YY)]),
    ALLEXCEPT('Table (2)','Table (2)'[Quarter]),
    'Table (2)'[Date (DD/MM/YY)] < maxdate
)
RETURN
IF(
    maxdate = curdate,
    1,
    IF(
        curdate = secondmaxdate,
        2,
        0
    )
)

If this solution helps, then kindly give it a kudos and mark it as a solution.