Concatenex with Rank

Hi Jyaul1122 , I have achieved your expected output as below, have attached the PBIX file along with this post

Download PBIX

Did I answer your question ? If yes, please mark this post as a solution.

Thanks,

Jai 

Hi Jyaul1122 ,

You can produce your required output by writing a measure like below after creating a calendar table (DateTable), and creating a relationship between your fact table and the calendar dimension table.

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'[Date],
                ,
                ASC
            ), "0"
        ) & " " & 'Stage table'[Stage]
    ),
    [Rank_Stage],
    ", ",
    'Stage table'[Project],
    ASC
)

The resulting output will be as shown below, which is in line with your required output. 

I've attached an example pbix file for your reference.

Best regards,

Jyaul1122 

Add a calculated column in your Stage table to extract the year from the date:

Year = YEAR('Stage table'[Date])

Create a Rank Measure

RankStage = 
RANKX(
FILTER(
'Stage table',
'Stage table'[Project] = MAX('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = YEAR(MAX('Stage table'[Date]))
),
'Stage table'[Date],
, ASC,
DENSE
)

Create the Combined Measure:

CombinedStageRank = 
CONCATENATEX(
FILTER(
'Stage table',
'Stage table'[Project] = MAX('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = MAX('Stage table'[Year])
),
RANKX(
FILTER(
'Stage table',
'Stage table'[Project] = MAX('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = YEAR(MAX('Stage table'[Date]))
),
'Stage table'[Date],
, ASC,
DENSE
) & " " & 'Stage table'[Stage],
", ",
'Stage table'[Date],
ASC
)

Create a Matrix Visual:

Use the Project field for the Rows.
Use the Year field for the Columns.
Use the CombinedStageRank measure for the Values.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Kedar_Pande DataNinja777 

Thanks for your time.

The replies are close to solution, what if the two or more stage have same dates. For example Project 4: Stage 3 and Stage 5 are have same date. As per your logic I am getting rank 1 for both but I would like to get: 

Data: