Display second record (max-1) based on selected end date

[siddharth_kulka]

Hello,

Sample app and dummy data attached. App Data model is very much on similar lines (UI_EndDate is an island and not Joined/Linked to the fact table).

Scenario: Based on selected end date (Always one selected), display 'Limit' value for 2nd Max Analysis Date (Max [Analysis Date] -1) for rolling 12 month period in fact data.

3 examples to clarify:

1. If selected UI_EndDate is 11/4/2018, straight table should display below:

2. If selected UI_EndDate is 10/10/2018, straight table should display below:

3. If selected UI_EndDate is 10/1/2018, straight table should display below:

Any help, much appreciated.

Cheers!

[tresesco]

Try this expression :

Sum({<[Analysis Date]={"=Rank(If([Analysis Date]<=UI_EndDate,[Analysis Date]))=2"}>} Limit)

[siddharth_kulka]

Hi Tresesco,

Firstly, thanks. Your feedback is accurate based on the question. However, realized I didnot communicate the requirement in its entirety.

Scenario (corrected): Based on selected end date (Always one selected), display 'Limit' value for 2nd Max Analysis Date (Max [Analysis Date] -1) for every record in rolling 12 month period. Re-attaching qvw and sample date.

Expected Output (Column 'Previous Value' needs to be calculated):

Thanks in advance!