Latest values for latest date based on selection

Santosh_K — Thu, 29 Jul 2021 14:56:42 GMT

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

Re: Latest values for latest date based on selection

Kushal_Chawda — Thu, 29 Jul 2021 22:01:06 GMT

@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))

topic Re: Latest values for latest date based on selection in App Development

Latest values for latest date based on selection

Re: Latest values for latest date based on selection