Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Analystmate
Helper II
Helper II

Please help me with the DAX formula to achieve desired results.

Hi All,

 

 

I need your help to create a calculated column based on conditions

 

Refer to the below sample data I have financial year-wise txn I want to categorize all txn based on condition. if any ref order exists in last year txn then mark it as "Partpayment"  Else "NO"  in the Part-payment Status field . (refer to desired results )

 

 

Please help me with the DAX formula to achieve desired results.

 

 

Ref No_                                FY         Status (desired results)

NK/20-21/011872             FY-20    NO

NK/20-21/006104             FY-20    NO

NK/19-20/146318             FY-20    NO

NK/20-21/002702             FY-20    NO

NK/20-21/001449             FY-21    NO

NK/20-21/014478             FY-21    NO

NK/20-21/006104             FY-21    Partpayment

NK/20-21/016876             FY-21    NO

NK/20-21/002702             FY-21    Partpayment

NK/19-20/134890             FY-21    NO

NK/20-21/000143             FY-21    NO

NK/20-21/008019             FY-21    NO

NK/19-20/106009             FY-22    NO

NK/19-20/083291             FY-22    NO

NK/20-21/000143             FY-22    Partpayment

NK/20-21/002906             FY-22    NO

NK/20-21/000292             FY-22    NO

NK/20-21/001449             FY-22    Partpayment

NK/20-21/006817             FY-22    NO

NK/20-21/014478             FY-22    Partpayment

 

 

Thanks

 

Lavdeep

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Analystmate ,

 

Try the following calculated column:

 

Status =
VAR FY_Value = 'Table'[FY]
VAR Reference_Number = 'Table'[Ref No_]
VAR temp_table =
    SUMMARIZE (
        ALL ( 'Table'[Ref No_], 'Table'[FY] ),
        'Table'[Ref No_],
        'Table'[FY],
        "IDCOLUMN",
            'Table'[Ref No_] & "FY-"
                & RIGHT ( 'Table'[FY], 2 ) + 1
    )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                temp_table,
                [IDCOLUMN]
                    = Reference_Number & "FY-"
                        & ( RIGHT ( FY_Value, 2 ) )
            )
        ) > 0,
        "PartPayment",
        "NO"
    )

 

MFelix_0-1627637358840.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @Analystmate ,

 

Try the following calculated column:

 

Status =
VAR FY_Value = 'Table'[FY]
VAR Reference_Number = 'Table'[Ref No_]
VAR temp_table =
    SUMMARIZE (
        ALL ( 'Table'[Ref No_], 'Table'[FY] ),
        'Table'[Ref No_],
        'Table'[FY],
        "IDCOLUMN",
            'Table'[Ref No_] & "FY-"
                & RIGHT ( 'Table'[FY], 2 ) + 1
    )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                temp_table,
                [IDCOLUMN]
                    = Reference_Number & "FY-"
                        & ( RIGHT ( FY_Value, 2 ) )
            )
        ) > 0,
        "PartPayment",
        "NO"
    )

 

MFelix_0-1627637358840.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.