Rank function Based on ReportedDate like YYYYMM

[Sandeep13]

Hi All,

I have 2 columns reporting_mth and Amount, I want to create rank based on reporting_mth and sort data based on amount.

Now If you see i have 202203 and 202212 in my data. I am expecting ouput like  

I am expecting output like below .if we have 2 same reportingmonth with different monthnumber  than always give  max Outstanding Amount as rank 1.

reporting_mth	Outstanding Amount	Rank
201803	2996300950580	1
201903	3431078559335	1
202003	3865145306221	1
202103	4057019515671	1
202203	4406709276776	2
202212	4992165757965	1

@ranku @PoiwerBInovice @po @PowerZ @PBCommunity 

[Anonymous]

Hi @Sandeep13 

You can create a measure

Measure = var a=LEFT(SELECTEDVALUE('Table'[reporting_mth]),4)
return RANKX(FILTER(ALLSELECTED('Table'),LEFT([reporting_mth],4)=a),CALCULATE(SUM('Table'[Outstanding Amount])),,DESC,Dense)

Best Regards!

Yolo Zhu

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

[Ahmedx]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RiHACZ_Smu67Qkufg?e=tdVPVU

[Sandeep13]

Hey While using your code i am getting error massage.

the query referenced calculated column which does not hold any data there is an error in its expression.

[Ahmedx]

Share sample pbix file to help you.

[Sandeep13]

Hi Thanks for your reply.

For me I dont see attached option here becuase I am not a super user.

I have uploaded file in powerbi services. below is link for your refernce,.

Let me know if you are not able to open the link.
If possible share me your email id so i can give you persmission dirclty 

Power BI

[Ahmedx]

Order = 
VAR _t = LEFT( MAX( [reporting_mth],4))
RETURN 
    RANKX(
         FILTER(ALL('Table'), 
          LEFT('Table'[reporting_mth],4)=_t),
            [reporting_mth],,DESC)

[Sandeep13]

Still some issue.
I am Passing dax in measure.

Getting below errro msg.

A single value for column 'reporting_mth' in table 'Port_Append' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Ahmedx]

pls try this

[Ahmedx]

you create a column, you don't need to create a measure

[Sandeep13]

Hi thank you for your response but its give me error.

reporting_mth is a column in my data.