5 Replies Latest reply on Aug 30, 2018 5:24 AM by Mahfooj Khan

# How to get sum of a measure based on maximum date within a selected time range ?

I have a below dataset which is restricted with a filter provided between dates 12/01/2018 to 14/01/2018

 Name of Store Visit date No of Devices Latest visit date Count of devices during latest visit Store -01 12/01/2018 2 14/01/2018 1+5=6 Store -01 13/01/2018 3 14/01/2018 1+5=6 Store -01 14/01/2018 1 14/01/2018 1+5=6 Store -02 12/01/2018 3 14/01/2018 1+5=6 Store -02 13/01/2018 2 14/01/2018 1+5=6 Store -02 14/01/2018 5 14/01/2018 1+5=6

I need to derive logic for column-3 and column-4 in the above dataset.

Logic: For a particular store if the latest visit is 14th Jan within a selected filter range then it should count all the no.of devices for that latest visit date.

For example, If now the range is changed to 12/01/2018 to 13/01/2018 then the result should be like below :

 Name of Store Visit date No of Devices Latest visit date Count of devices during latest visit Store -01 12/01/2018 2 13/01/2018 3+2=5 Store -01 13/01/2018 3 13/01/2018 3+2=5 Store -02 12/01/2018 3 13/01/2018 3+2=5 Store -02 13/01/2018 2 13/01/2018 3+2=5

Thanks in advance.

Regards,

Sukriti

• ###### 1. Re: How to get sum of a measure based on maximum date within a selected time range ?

Hi,

You may try this,

{SUM(IF {FIXED [Name of Store]:MAX([Visit date])}<=[Visit date] THEN [No of Devices] END)}

Note: Put your filters on Context

Changing range of dates then

Let us know if this help.

Mahfooj

• ###### 2. Re: How to get sum of a measure based on maximum date within a selected time range ?

Hi,

Create a calculated field.

IF [Visit date]={MAX([Visit date])}

THEN [No of Devices]

END

Create your view.

Put date filter to context.

• ###### 3. Re: How to get sum of a measure based on maximum date within a selected time range ?

Hi SUkriti,

You can try below approach:

Num of device on last visit =

{SUM(if [Visit date]={max([Visit date])} then [No of Devices] END)}

Add "Visit Date"to context filter.

• ###### 4. Re: How to get sum of a measure based on maximum date within a selected time range ?

Thank you all for your quick replies.

In my dashboard I am using some quick filters along with Visit date. Is there any other way in which above problem can be resolved without taking Visit date as 'Context filter'.

Thanks

Sukriti

• ###### 5. Re: How to get sum of a measure based on maximum date within a selected time range ?

You can try with some table calculation.

I don't know whether it will work with your original data or not