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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Isidro
Helper IV
Helper IV

How calculate a column calculated with the year of the oldest invoice an another with the youngest

Hi,

 

I have the following table:

 

CUSTOMERINVOICE DATE
AAA14/02/2014
BBB14/06/2012
CCC13/02/2016
AAA15/03/2015
DDD10/06/2013
EEE20/12/2016
BBB06/08/2013
CCC22/09/2014
AAA22/10/2016
BBB14/11/2015

 

I want a column calculated with the year of the oldest invoice, and another column with the year of the most recent invoice.

 

CUSTOMERINVOICE DATEOLDEST INVOICEMOST RECENT INVOICE
AAA14/02/201420142016
BBB14/06/201220122015
CCC13/02/201620142016
AAA15/03/201520142016
DDD10/06/201320132013
EEE20/12/201620162016
BBB06/08/201320122015
CCC22/09/201420142016
AAA22/10/201620142016
BBB14/11/201520122015

 

Thank you very much and best regards.

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

Hi @Isidro

 

This shall get you the oldest invoice

=
CALCULATE (
    YEAR ( MIN ( [INVOICE DATE] ) ),
    FILTER ( Table1, Table1[CUSTOMER] = EARLIER ( Table1[CUSTOMER] ) )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

Hi @Isidro

This shall get you Most Recent Invoice

=
CALCULATE (
    YEAR ( MAX ( [INVOICE DATE] ) ),
    FILTER ( Table1, Table1[CUSTOMER] = EARLIER ( Table1[CUSTOMER] ) )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

Hi @Isidro

This shall get you Most Recent Invoice

=
CALCULATE (
    YEAR ( MAX ( [INVOICE DATE] ) ),
    FILTER ( Table1, Table1[CUSTOMER] = EARLIER ( Table1[CUSTOMER] ) )
)

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

It is great!!!

 

Thank you and best regards.

Zubair_Muhammad
Community Champion
Community Champion

Hi @Isidro

 

This shall get you the oldest invoice

=
CALCULATE (
    YEAR ( MIN ( [INVOICE DATE] ) ),
    FILTER ( Table1, Table1[CUSTOMER] = EARLIER ( Table1[CUSTOMER] ) )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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