Hi, I have a data set that looks like this,

I am adding a widget which displays the value in the 2nd column in the image for each date.

But if I am choosing a range of date, suppose May 1-31, I want to show the latest record which is against the date may 30 in my data set. How can I achieve this.

Consider the value column as closing balance for a day. for eg: my closing balance for may 1st is 100$ and on May 30th is 50$. so when I select the date range of may 1-31, the closing balance should be 50$ ideally .

currently am adding the value field as sum(value_column) and this returns the sum of all values in that range. But I want to fetch only the latest record

Hi @hari ,

This is a very interesting case, and I’d love to support you with it.

My understanding is that: when filtering a date range from A to B (B is after A), the widget will return the value of **the last date with a record** between A and B. Please correct me if I missed anything.

If yes then it’s a bit tricky at the moment, since Holistics doesn’t support window functions like LAST_VALUE in calculation field just yet.

A solution (or a hack, more like) to this: a dynamic Point-In-Time metric.

- For this, we’ll convert the
`Date`

timestamp into an integer, which we will sum with the`Closing balance`

value → to create a**surrogate value**.- Do note that this integer has to be big enough, so after summing with
`Closing balance`

value it won’t affect the order.

- Do note that this integer has to be big enough, so after summing with
- Then, we’ll use this
`surr_value`

integer to look for the last day (which is now the maximum value possible) in the period. - After that, a Business calculation to calculate the
**End-of-period**value should do it.

We’ve put together a detailed How-to doc: **How to Calculate Dynamic Point-In-Time Metrics in Holistics | Holistics Docs**.

The example in this doc is not exactly the same as your case, but it’s quite close and can make a good reference.

Please try this out, and let me know should you have more questions. Cheers

Hi @vu.duong ,

Thanks for the response.

The closing balance on a day need not be always greater than the previous day. This is more like outstanding payment from a customer. so lets say a guy owes me 100$ today, the closing balance will be 100 and if he pays me 20$ the next day, the closing balance will be 80$ tomorrow.so the surrogate value when sorted in increasing order, need not always give me the value on last day at the last row.