cancel
Showing results for
Did you mean:  Helper III

## 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 III

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 III

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 III

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 III

What if there is more than one previous row?  Super User III

Change =1 to >=1

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

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 III

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 #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  