Latest values for latest date based on selection

[Santosh_K]

Hi All,

I need help on below scenario. I have data as below

LOAD * Inline [
Acct,Div,Date,Value
1,a,01/01/2021,100
1,b,01/02/2021,60
1,b,01/03/2021,80
2,b,01/01/2021,90
2,a,01/02/2021,50
2,a,01/03/2021,70]

Let's say I have selected month 01/03/2021 and Div is not selected I would always like to show latest Value for Acct. If for that period value is not available, need to go back to previous periods to select latest available Value. I am using below measure which is working fine in this case

FirstSortedValue(distinct {<Date ={"<=$(vMaxDate)"}>} Value, -Date)

where vMaxDate = max(Date)

Now, when I select month 01/03/2021 and Div = 'a' , for Acct =1, I would like to show value as 0, because for this Acct, latest Date has Div=b. Similarly, for Acct=2, I would like to show value as 70, because for this Acct, latest Date has Div=a

Acct   Value

1             0

2             70

In short, when I select any Div, particular Acct should only get value if that Acct's latest Date has that selected Div.

Similarly, when I select month 01/03/2021 and Div = 'b' ,  I should get below

Acct   Value

1             80

2             0

I am not able to get around the expression when particular Div is selected

[Kushal_Chawda · Accepted solution]

@Santosh_K  If I understood it correctly, you can try something like below. Make sure that "Include zero values" option is checked in add-on->data handling 

if(GetSelectedCount(Div)=0, FirstSortedValue(distinct {<Date={"<=$(=date(max(Date)))"}>}Value, - Date),
sum(distinct {<Date={"$(=date(max(Date)))"}>}Value))