Forum Discussion
Dynamic Filter field & summaries over time
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.
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
1 Reply
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