Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
Download the PBI file from here.
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.
What I have is a 2 column table.
3 column table is my expected output 3rd column is calculated.
I am absolutely confused now. I have created the 3rd column for your in Table1 as per your initial request.
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:
My formula returns the third column shown in your Final Output Table.
Can you share the pbix?
Download the PBI file from here.
User | Count |
---|---|
84 | |
74 | |
63 | |
51 | |
45 |
User | Count |
---|---|
101 | |
43 | |
41 | |
39 | |
36 |