Put custom value between given dates

[mussaenda]

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

[Ashish_Mathur]

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.

[mussaenda]

Value is manual input.

How can I input the value?

[Ashish_Mathur]

I do not understand your question/comment.  The Data1 Table is a 3 column Table.

[mussaenda]

What I have is a 2 column table.

3 column table is my expected output  3rd column is calculated.

[Ashish_Mathur]

I am absolutely confused now.  I have created the 3rd column for your in Table1 as per your initial request.

[mussaenda]

Above photo is my data.

then I will put these values based on date on a calculated column. It is not existing on my current data.

After the calculated column created, this is the expected output:

Then if i will create a visual, this is the expectec output:

[Ashish_Mathur]

My formula returns the third column shown in your Final Output Table.

[mussaenda]

Can you share the pbix?

[Ashish_Mathur · Accepted solution]

Download the PBI file from here.