Previous Date Issue

[Rehan]

I have a Extraction Date field but it has no sequence to it. For example I have dates like

05/1/2019

03/1/2019

2/1/2019.

I always want the Max Date and the  Previous from Max Date. In this case I need 5/1/2019 and 3/1/2019. I know how to get 5/1/2019 but how will get  3/1/2019. 

[Vegar]

Use the Max() function with two parameters like I've done in the picture below.

[Rehan]

HOw would you use in the set analysis for Sales

Sum({<Date={$(=Max(Date))}>} Sales)

What to write for Max(Date,2)?

[Vegar]

I would write it like this

=Sum({<Year={"$(=max(Year,2))"}>}Sales)

[Rehan]

Your solution works fine if I dont make a selection in ETL Date field. 

05/1/2019

03/1/2019

2/1/2019

My expression is below which is good

Sum({<[ETL Date]={"$(=MAX([ETL Date],2))"} >} [Amount]) 

It gives me the data for 3/1/2019, But If I make a selection in the ETL date field everything blanks out. The requirement is that by default  it is going to be 3/1/2019 but If I make a selection as 3/1/2019 this expression will give me the data for 2/1/2019

How can I accomplish that ??

[Vegar]

Try this instead:

Sum({<[ETL Date]={'$(=date(Max([ETL Date],2)),'DD/M/YYYY')'} >} [Amount])

[Rehan]

It doesn't work. I don't get anything If I add the below expression

[Vegar]

Could you attach a sample app or sample data ? It will make it easier to troubleshoot. 

[Rehan]

Attached please find the sample data.. In this case by default with no selection in the extract date

I would like to calculate the amount for Max(Extract Date) and one previous date

1/25/2020 and 1/20/2020

but if the user selects 1/20/2020 the result will be 

1/20/2020 and 1/15/2020

[Rehan]

Solved it, Created a variable vETL Date as

=Date(If(GetSelectedCount([ETL Date]) >0,
Aggr(Above(Max({<[ETL Date]>} [ETL Date])), [ETL Date]), Max({<[ETL Date]>}[ETL Date], 2)))

and used that in the expression