Forum Discussion

Jyaul1122's avatar
Jyaul1122
Helper III
1 year ago
Solved

Concatenex with Rank

Hello friend,    I have Stage table with fields: Project,Stage and Date. Project Stage Date Project 1 S1 25-Dec-24 Project 1 S2 27-Dec-24 Project 1 S3 27-Dec-24 Project 1 S...
  • Jyaul1122's avatar
    1 year ago

    DataNinja777 Kedar_Pande 

    I solved by editing above measures.

    First I created one column Sorting in Power query from date column and stage

    Date.Year([Date])*100000000
    +Date.Month([Date])*1000000
    +Date.Day([Date])*10000
    +Text.End([Stage],1)

     

    and measure edit:

     

    Stage Measure = 
    VAR CurrentYear = SELECTEDVALUE('DateTable'[Year]) -- Gets the selected year from the DateTable
    VAR Projects = DISTINCT('Stage table'[Project]) -- Get distinct projects for iteration
    RETURN
    CONCATENATEX(
        ADDCOLUMNS(
            FILTER(
                'Stage table',
                YEAR('Stage table'[Date]) = CurrentYear
            ),
            "Rank_Stage", 
            FORMAT(
                RANKX(
                    FILTER(
                        'Stage table',
                        'Stage table'[Project] = EARLIER('Stage table'[Project]) &&
                        YEAR('Stage table'[Date]) = CurrentYear
                    ),
                    'Stage table'[Sorting],
                    ,
                    ASC
                ), "0"
            ) & " " & 'Stage table'[Stage]
        ),
        [Rank_Stage],
        ", ",
        'Stage table'[Date],
        ASC
    )