How to calculate Max and Second largest date measure

[Anonymous]

Hi All,

 How to calculate Max and Second largest date measure using Created Date column and calculate Total amount where Invoice date less than equal to last date and greater than equal to second largest Date.

Raw Data:-
Product	Invoice Date	Create Date	Cust Id	amt
Prod1	7/11/2019	7/5/2019	1001	1.2
Prod1	7/10/2019	7/6/2019	1001	0.4
Prod1	7/9/2019	7/7/2019	1001	0.3
Prod2	7/8/2019	7/8/2019	1001	1.5
Prod2	7/10/2019	7/10/2019	1001	2.5
Prod3	7/11/2019	7/13/2019	1001	0.4
Prod1	7/15/2019	7/10/2019	1002	1.2
Prod2	7/8/2019	7/11/2019	1002	0.4
Prod3	7/11/2019	7/12/2019	1002	0.3
Prod3	7/14/2019	7/15/2019	1002	1.5
Prod1	7/10/2019	7/8/2019	1003	2.5
Prod2	7/11/2019	7/10/2019	1003	1.2
Prod3	7/15/2019	7/12/2019	1003	0.4
Prod1	7/10/2019	7/11/2019	1004	0.3
Prod2	7/11/2019	7/12/2019	1004	1.5
Prod3	7/15/2019	7/15/2019	1004	2.5

Output:-
Product	Pre Date	Last Date	Cust Id	Amt
Prod1	7/10/2019	7/13/2019	1001	1.2
Prod1	7/10/2019	7/13/2019	1001	0.4
Prod1	7/10/2019	7/13/2019	1001	2.5
Prod2	7/10/2019	7/13/2019	1001	0.4
Prod1	7/12/2019	7/15/2019	1002	1.2
Prod3	7/12/2019	7/15/2019	1002	1.5
Prod1	7/10/2019	7/12/2019	1003	2.5
Prod2	7/10/2019	7/12/2019	1003	1.2
Prod3	7/10/2019	7/12/2019	1003	0.4
Prod3	7/12/2019	7/15/2019	1004	2.5

Thanks,

Varsri

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

Try these MEASURES

Last_date =
CALCULATE ( MAX ( Table1[Create Date] ), ALLEXCEPT ( Table1, Table1[Cust Id] ) )

Second_Last_Date =
VAR Mydate = [Last_date]
RETURN
    CALCULATE (
        MAX ( Table1[Create Date] ),
        ALLEXCEPT ( Table1, Table1[Cust Id] ),
        Table1[Create Date] < MyDate
    )