Dynamic Filter field & summaries over time

[WokkedElite]

Hi

I have some data that looks somewhat like this

Comment	Comment Date Recieved	Reply Date Sent	Status
Bla	1/2/24	1/5/24	Closed
Bla bla	5/11/23	23/2/24	Closed
Yes	13/4/24		Open
No	4/12/23		Open

I want to try and generate the following over a time graph

total days comments open
- for "open" comments, sum of days between date received and the date of calculation but only for +ve values. (i.e. only starts counting days open after being recieved)
- for "closed" comments, sum of days between date recieived and the date of calculation but only until the date closed, after which it is removed from the summed value.
These should be time dynamic, such that a graph of the total of this measure over time increases daily (i.e. 5 comments open = +5 days per day, but if a comment with 40 days swaps to "closed" it goes -40).

This feels tricky and not sure if it's in the remit of PBI to be able to "Flip" a filter value based on dates over a time-based calculation.

[Anonymous · Accepted solution]

Hi  @WokkedElite ,

Here are the steps you can follow：

1. Create calculated column.

Value1 =
IF(
    [Status]="Open",
    DATEDIFF(
        [Comment Date Recieved],TODAY(),DAY),
    DATEDIFF(
        [Comment Date Recieved],[Reply Date Sent],DAY))

Value2 =
IF(
    [Status]="Open",
    DATEDIFF(
        [Comment Date Recieved],TODAY(),DAY),
    DATEDIFF(
        [Comment Date Recieved],[Reply Date Sent]-1,DAY)-40)

2. Result:

If the above results do not meet your expectations, can you express the expected results in the form of a picture, we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly