Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks,
I need help in calculating the cumulative frequencies row wise with minimum date and maximum date selection by users using sliders. Here is the table that i want to generate could you please guid me ? I've tried various function and methods but nothing is giving me right answer. Thanks a lot in advance.
Below is the table that i've and i want to generate,
ID | Date | values |
1 | 15-08-19 | 12 |
2 | 16-08-19 | 15 |
3 | 17-08-19 | 48 |
4 | 18-08-19 | 65 |
5 | 19-08-19 | 94 |
6 | 20-08-19 | 5 |
7 | 21-08-19 | 26 |
8 | 22-08-19 | 45 |
9 | 23-08-19 | 59 |
10 | 24-08-19 | 36 |
11 | 25-08-19 | 21 |
12 | 26-08-19 | 5 |
13 | 27-08-19 | 42 |
ID | Date | values | Cumulative Values row wise | Date Slider | ||
1 | 15-08-19 | 12 | ||||
2 | 16-08-19 | 15 | From | 17-08-19 | ||
3 | 17-08-19 | 48 | 48 | |||
4 | 18-08-19 | 65 | 113 | To | 25-08-19 | |
5 | 19-08-19 | 94 | 207 | |||
6 | 20-08-19 | 5 | 212 | |||
7 | 21-08-19 | 26 | 238 | |||
8 | 22-08-19 | 45 | 283 | |||
9 | 23-08-19 | 59 | 342 | |||
10 | 24-08-19 | 36 | 378 | |||
11 | 25-08-19 | 21 | 399 | |||
12 | 26-08-19 | 5 | ||||
13 | 27-08-19 | 42 |
Solved! Go to Solution.
Hi @Anonymous ,
You could use this mesure. You also need a Product slicer and a Date slicer.
Measure 2 = CALCULATE ( SUM ( test[value] ), FILTER ( ALLSELECTED ( test ), test[ID] <= MAX ( test[ID] ) ) )
Here is the result.
Hi @Anonymous,
I trust you are doing well.
Please have a look at the below screenshots:
Please mark as answer if correct 🙂
Kind Regards
Bash
Hi,
Thanks for your reply. I have gone throgh the solution and screenshot & replicated it in my soultion. But, it is not giving me expected results. When i am sliding up and down the dates it is disturbing the result. Can we use somehow min date and max date which is from slider in the same query to compute the results ? I tried multiple queries but not getting correct answer.
Hi @Anonymous,
Im not sure why its not working since i have duplicated the information you gave and placed it into an excel spreadsheet and got the desired amounts that you posted.
Could you elaborate abit more on the issue?
Are all these fields in one table ?
Can you show me your formula?
Kind Regards
Bash
Try creating a quick measure- Running total - Base value would be your value field - the other value would be your ID field - leave the asceding as is.
Hi,
Ok, I will try it and share the query with you. Allow me some time.
Here is the complete table and includes all the avriables,
in the dashboard we have one filter for Product and one slider for date. Using both i am calculating cumulative sum.
ID | Product | Date | Value | CumSum | Filter for Product | Slider for Date | ||||
1 | A | 26-Sep | 5 | A | Choosed | 28-Sep | From | |||
2 | A | 27-Sep | 6 | B | ||||||
3 | A | 28-Sep | 9 | 9 | C | 3-Oct | To | |||
4 | A | 29-Sep | 8 | 17 | ||||||
5 | A | 30-Sep | 74 | 91 | ||||||
6 | A | 1-Oct | 5 | 96 | ||||||
7 | A | 2-Oct | 6 | 102 | ||||||
8 | A | 3-Oct | 8 | 110 | ||||||
9 | A | 4-Oct | 2 | |||||||
10 | A | 5-Oct | 2 | |||||||
11 | B | 26-Sep | 6 | |||||||
12 | B | 27-Sep | 9 | |||||||
13 | B | 28-Sep | 8 | |||||||
14 | B | 29-Sep | 6 | |||||||
15 | B | 30-Sep | 3 | |||||||
16 | B | 1-Oct | 2 | |||||||
17 | C | 26-Sep | 5 | |||||||
18 | C | 27-Sep | 9 | |||||||
19 | C | 28-Sep | 8 | |||||||
20 | C | 29-Sep | 4 | |||||||
21 | C | 30-Sep | 2 | |||||||
22 | C | 1-Oct | 36 | |||||||
Hi @Anonymous ,
You could use this mesure. You also need a Product slicer and a Date slicer.
Measure 2 = CALCULATE ( SUM ( test[value] ), FILTER ( ALLSELECTED ( test ), test[ID] <= MAX ( test[ID] ) ) )
Here is the result.
Hi,
Thanks for your reply. This solution is giving me the results.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |