Forum Discussion
Put custom value between given dates
Hi,
I have this data:
Cust No Posting Date
| C10001 | 10/02/2019 |
| C10001 | 26/02/2019 |
| C10001 | 10/04/2019 |
| C10001 | 30/04/2019 |
| C10001 | 30/04/2019 |
| C10001 | 08/05/2019 |
| C10001 | 15/05/2019 |
| C10001 | 15/05/2019 |
| C10001 | 15/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 29/05/2019 |
| C10001 | 30/06/2019 |
| C10001 | 30/06/2019 |
| C10001 | 30/06/2019 |
| C10001 | 30/06/2019 |
| C10001 | 30/06/2019 |
| C10001 | 02/07/2019 |
| C10001 | 14/08/2019 |
| C10001 | 14/08/2019 |
Then I would like to give a custom value like this:
24/02/2019 - 31/10/2019 : 242169
23/03/2019 - 31/10/2019 : 363003
11/07/2019 - 31/12/2019 : 216071
So Final output will be:
Cust No Posting Date Value(calculated)
| C10001 | 10/02/2019 | 242169 |
| C10001 | 26/02/2019 | 242169 |
| C10001 | 10/04/2019 | 605172 |
| C10001 | 30/04/2019 | 605172 |
| C10001 | 30/04/2019 | 605172 |
| C10001 | 08/05/2019 | 605172 |
| C10001 | 15/05/2019 | 605172 |
| C10001 | 15/05/2019 | 605172 |
| C10001 | 15/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 29/05/2019 | 605172 |
| C10001 | 30/06/2019 | 821243 |
| C10001 | 30/06/2019 | 821243 |
| C10001 | 30/06/2019 | 821243 |
| C10001 | 30/06/2019 | 821243 |
| C10001 | 30/06/2019 | 821243 |
| C10001 | 02/07/2019 | 821243 |
| C10001 | 14/08/2019 | 821243 |
| C10001 | 14/08/2019 | 821243 |
| C10001 | 14/11/2019 | 216071 |
Adding the value depends on the date range.
I need to show this in data (column) not in a table visual.
This is possible, right?
Thank you!
If i will put this in a table visual, this will be the output:
Date Slicer: 14/08/2019
Table Visual:
C10001 821243
Date Slicer: 29/05/2019
Table Visual:
C10001 605172
Download the PBI file from here.
9 Replies
Hi,
This calculated column formula works
=CALCULATE(SUM(Data1[Value]),FILTER(Data1,Data1[Lower]<=EARLIER(Data[Posting Date])&&Data1[Upper]>=EARLIER(Data[Posting Date])))
Hope this helps.
Value is manual input.
How can I input the value?
I do not understand your question/comment. The Data1 Table is a 3 column Table.
