Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I would like to make a chart to track the status of my requests, by day.
Example with the following data:
ID | Created | Approved | Closed |
1 | 01/07/2019 | 03/07/2019 | |
2 | 01/07/2019 | 05/07/2019 | 06/07/2019 |
3 | 02/07/2019 | 02/07/2019 | |
4 | 03/07/2019 | 07/07/2019 | 10/07/2019 |
5 | 03/07/2019 | ||
6 | 03/07/2009 | 05/07/2019 | |
7 | 05/07/2019 | 08/07/2019 | 08/07/2019 |
8 | 06/07/2019 | 06/07/2019 | 09/07/2019 |
9 | 09/07/2019 | 10/07/2019 | |
10 | 09/07/2019 | 09/07/2019 |
As a table, I would like to obtain the following result:
Created | Approved | Closed | |
01/07/2019 | 2 | 0 | 0 |
02/07/2019 | 2 | 1 | 0 |
03/07/2019 | 4 | 2 | 0 |
04/07/2019 | 4 | 2 | 0 |
05/07/2019 | 3 | 4 | 0 |
06/07/2019 | 3 | 4 | 1 |
07/07/2019 | 2 | 5 | 1 |
08/07/2019 | 1 | 5 | 2 |
09/07/2019 | 2 | 5 | 3 |
10/07/2019 | 1 | 5 | 4 |
And as a chart :
Is this possible in Power BI ?
Thanks in advance,
Florent
Hi florentbignier,
If you want to calculate cumulative value, you could follow below steps:
You could create a calendar table by
calendar = CALENDAR(DATE(2019,7,1), DATE(2019,7,10))
Then create measure like below
apporve = CALCULATE(COUNT(stackchart[Approved]), FILTER(stackchart,stackchart[Approved]<=SELECTEDVALUE('calendar'[Date]))) closed = CALCULATE(COUNT(stackchart[Closed]), FILTER(stackchart,stackchart[Closed]<=SELECTEDVALUE('calendar'[Date]))) cretaed = CALCULATE(COUNT(stackchart[ID]), FILTER(stackchart,stackchart[Created]<=SELECTEDVALUE('calendar'[Date])))
Then you will get below result
But this seems not be same as your result. I am not clear about your first two columns' logic, so if possible, could you please explain for me in details? Then I will help you more correctly.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks dax for your answer and your time 🙂
In fact, when a request has an approved date, I don't want it to count as created.
Example with the firts 3 days :
July 01 : Requests #1 and #2 were created. There are 2 requests created, 0 approved and 0 closed
July 02 : Request #3 was created, then it was approved. There are now 3 requests : 2 created (#1 and #2) and 1 approved (#3)
July 03 : Requests #4, #5 and #6 were created. Request #1 was approved. There are now 4 requests created (#2, #4, #5 and #6), 2 approved (#1 and #3) and 0 closed
That's it I don't know how to do 😞
Thanks in advance.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |