Find the latest 2 dates

[axk180022]

Expected output

CALCULATIONDATETIME	INVNETSERIALID	GENERATORHOURS
9/25/2020	CVS-002	8154
11/23/2020	CVS-002	8359
5/13/2021	CVS-003	9122
5/27/2021	CVS-003	9159

I have this table, I need just filter the 2 latest dates for each inventserialID having an existing Generator hours. 

2 LAST DATES = 
Var _invent = FIRSTNONBLANK('Table'[INVENTSERIALID],"")
vAR _latestdate = CALCULATE(max('Table'[CALCULATIONDATETIME]),ALLEXCEPT('Table','Table'[INVENTSERIALID]))
vAR _2ndlatest = MAXX(FILTER(filter('Table','Table'[INVENTSERIALID]=_invent),'Table'[CALCULATIONDATETIME]<_latestdate),'Table'[CALCULATIONDATETIME])
RETURN
IF('Table'[CALCULATIONDATETIME] IN {_latestdate,_2ndlatest},'Table'[CALCULATIONDATETIME])

The above worked but does not take the generator hours that has existing value in it, just takes max and 2nd max date for each serialID. Can someone tell me what changes need to be made.

I require last and second last date for each serialid having an existing genratorhours( highlighted in yellow).

[parry2k]

@axk180022 

easure 2 = 
CALCULATE ( 
    SUM ( Inv[GENERATORHOURS] ),
    KEEPFILTERS ( 
        TOPN ( 
            4, 
            FILTER ( 
                ALLSELECTED ( Inv ), 
                Inv[INVENTSERIALID] = MAX ( Inv[INVENTSERIALID] ) && 
                Inv[GENERATORHOURS] > 0 
            ), 
            Inv[CALLACTIONDATETIME], DESC 
        ) 
    ) 
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[axk180022]

Hi,

This measure worked perfectly for me, but I would want to consider the ones which has values, it is picking up the blanks for top 4, can you please tell me what needs to be included.

@parry2k 

[parry2k · Accepted solution]

@axk180022 and here is the output:

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@axk180022 try this measure:

Measure 2 = 
CALCULATE ( 
    SUM ( Inv[GENERATORHOURS] ),
    KEEPFILTERS ( 
        TOPN ( 
            2, 
            FILTER ( 
                ALLSELECTED ( Inv ), 
                Inv[INVENTSERIALID] = MAX ( Inv[INVENTSERIALID] ) && 
                Inv[GENERATORHOURS] > 0 
            ), 
            Inv[CALLACTIONDATETIME], DESC 
        ) 
    ) 
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[axk180022]

Hi Parry,

Can you please tell me what should be the change in the measure if I need last 4 dates? 

@parry2k 

[parry2k]

@axk180022 can you paste the data in the table instead of the image.

[axk180022]

CALLACTIONDATETIME	INVENTSERIALID	GENERATORHOURS
6/23/2020 14:55	CVS-002	2600
6/24/2020 12:31	CVS-002	7749
6/25/2020 17:32	CVS-002	7749
7/28/2020 13:10	CVS-002	7749
8/19/2020 18:30	CVS-002	0
8/31/2020 13:15	CVS-002	0
9/19/2020 14:00	CVS-002	8103
9/25/2020 21:30	CVS-002	8154
11/18/2020 17:00	CVS-002	0
11/23/2020 19:40	CVS-002	8359
12/16/2020 13:30	CVS-003	8479
3/9/2021 16:43	CVS-003	8804
4/27/2021 21:55	CVS-003	5107
5/13/2021 15:41	CVS-003	9122
5/27/2021 14:00	CVS-003	9159
6/16/2021 19:07	CVS-003	0

@parry2k