cancel
Showing results 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

Resolver I

## How to identify new and Old Txn based on Previous year data ?

Hi

I need your help to create a calculated column.

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

 Ref  No_ FY Status (desired results) 011872 FY-20 NEW 006104 FY-20 NEW 146318 FY-20 NEW 002702 FY-20 NEW 001449 FY-21 NEW 014478 FY-21 NEW 006104 FY-21 OLD 016876 FY-21 NEW 002702 FY-21 OLD 134890 FY-21 NEW 000143 FY-21 NEW 008019 FY-21 NEW 106009 FY-22 NEW 083291 FY-22 NEW 000143 FY-22 OLD 002906 FY-22 NEW 000292 FY-22 NEW 001449 FY-22 OLD 006817 FY-22 NEW 014478 FY-22 OLD

2 ACCEPTED SOLUTIONS
Super User

You didn't specify if you wanted a measure or a calculated column. Here is a calculated column.

``````Status =
var r = 'Table'[Ref  No_]
var f = 'Table'[FY]
var c = CALCULATE(COUNTROWS('Table'),All('Table'),'Table'[Ref  No_]=r,'Table'[FY]<f)
return if(c=0,"New","Old")``````
Super User

Hi,

This calculated column formula works

``=if(CALCULATE(COUNTROWS(Data),FILTER(data,Data[Ref  No_]=EARLIER(Data[Ref  No_])&&Data[FY]<EARLIER(Data[FY])))=1,"Old","New")``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

Hi,

This calculated column formula works

``=if(CALCULATE(COUNTROWS(Data),FILTER(data,Data[Ref  No_]=EARLIER(Data[Ref  No_])&&Data[FY]<EARLIER(Data[FY])))=1,"Old","New")``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

What if there is more than one previous row?

Super User

Change =1 to >=1

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

Hi @lavdeepk ,

if you need a measure you can do it like this:

``````Status =
VAR _Min =
CALCULATE ( MIN ( 'Table'[FY] ), ALLEXCEPT ( 'Table', 'Table'[Ref  No_] ) )
RETURN
IF ( MIN ( 'Table'[FY] ) = _Min, "New", "Old" )
``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Super User

You didn't specify if you wanted a measure or a calculated column. Here is a calculated column.

``````Status =
var r = 'Table'[Ref  No_]
var f = 'Table'[FY]
var c = CALCULATE(COUNTROWS('Table'),All('Table'),'Table'[Ref  No_]=r,'Table'[FY]<f)
return if(c=0,"New","Old")``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.