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.
This is a follow-up from a previous post I made.
Firstly I'll leave some example data:
package_ID | client_ID | package_weight | due_date | arrival_date |
1 | 1 | 20 | 03-03-2021 | 05-03-2021 |
2 | 1 | 15 | 08-04-2021 | 02-04-2021 |
3 | 2 | 18 | 08-03-2021 | 12-03-2021 |
4 | 3 | 23 | 05-03-2021 | 03-03-2021 |
5 | 1 | 35 | 20-05-2021 | |
6 | 3 | 12 | 10-04-2021 | 31-03-2021 |
7 | 2 | 8 | 13-04-2021 | 20-04-2021 |
8 | 3 | 33 | 08-06-2021 | |
9 | 1 | 26 | 10-06-2021 | |
10 | 2 | 14 | 09-06-2021 | |
11 | 2 | 60 | 08-07-2021 | |
12 | 3 | 32 | 15-07-2021 | |
13 | 1 | 4 | 20-07-2021 |
Just in case, dates are D-M-Y. The example data assumes a current date of 25-04-2021. Some dates in the arrival_date column are intentionally blank, since those are due in the future. Those are filled in as the packages arrive.
I also have a date table that's joined by both due and arrival dates.
What I need to achieve is a line graph that has three elements:
1) Running total for package_weight by due_date.
2) Running total for package_weight by arrival_date.
3) A simple projection for total package_weight based on a simple estimate. Something like this: first calculating a "daily weight" for last "interval", ie. last package_weight divided by days between penultimate arrival_date and last arrival_date, and then multiplied by amount of days until a given date, eg. 31-07-2021. That should give a very simple projection for the period from last arrival_date to 31-07-2021.
All of this per client. This is, I need to be able to use a slicer to view the data corresponding to a single client.
An example graph of what I want to achieve here:
In the graph blue is running total of package_weight by due_date, orange is running total of package_weightby arrival_date, and red is the projection.
In the previous post I mentioned I received a suggestion that helped me to make one of the running sums work, but not both at the same time in the same graph.
Here's my code:
Cumm due = CALCULATE(CALCULATE(SUM(Table[package_weight]), USERELATIONSHIP(date[date], table[due_date])), FILTER(ALLSELECTED(table[due_date]), ISONORAFTER(table[due_date], MAX(table[due_date]), DESC)))
Cumm arrival = CALCULATE(CALCULATE(SUM(Table[package_weight]), USERELATIONSHIP(date[date], table[arrival_date]), FILTER(ALLSELECTED(table[arrival_date]), ISONORAFTER(table[arrival_date], MAX(table[arrival_date]), DESC)))
Blue is Cumm due and cyan is Cumm arrival. Only Cumm due seems to work.
This is also using due_date as the graph's x axis (Axis box). When I put arrival_date there, it's reversed, Cumm arrival works and Cumm due doesn't. It's kinda obvious it shouldn't work, as I should use the date[date] instead, but when I do, the graph is empty. I imagine this is due to me not using the dates table in my code.
And I'm also missing the projection, and here apparently the forecast functionality is of no help in this case because the dates are not at regular intervals.
Any help is appreciated, thanks in advance!
Hi @DusanVH
I guess the relationship between date[date] and table[arrival_date] is an active relationship in your model, so the code for cumm arrival doesn't work. Please remove the USERELATIONSHIP(date[date], table[arrival_date]) part from the code. USERELATIONSHIP is used to activate an inactive relationship in the code. For active relatioships, you don't need it.
Another reason is that arrival_date column has blank values for future dates. However, blank values are considered less than other non-blank values, so package_weight on those rows will be calculated incorrectly. We need to filter out blank dates in the code.
Below are my code
Cumm arrival = CALCULATE(SUM('Table'[package_weight]),FILTER(ALLSELECTED('Date'),NOT(ISBLANK('Date'[Date])) && 'Date'[Date]<=MAX('Date'[Date])))
Cumm due = CALCULATE(SUM('Table'[package_weight]), USERELATIONSHIP('Date'[Date], 'Table'[due_date]), FILTER(ALLSELECTED('Date'[Date]), 'Date'[Date]<=MAX('Date'[Date])))
And you should use 'date'[date] in both measures and on X Axis in the line chart. This will avoid missing any date value.
For the projection part, do you mean to divide the package_weight on the last arrival_date by days between penultimate arrival_date and last arrival_date? And how to get the ending given date? It's a fixed date or a dynamic date?
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @v-jingzhang , thanks for your suggestion, I'll try what you say, but I think you missed that it's per package. I think yours is adding up all packages, maybe you weren't using a slicer to select a package, I guess that should need no code modification. Also, your graphs are stepped, ideally they should be straight lines between each point.
For the projection part, do you mean to divide the package_weight on the last arrival_date by days between penultimate arrival_date and last arrival_date? And how to get the ending given date? It's a fixed date or a dynamic date?
package_ID | client_ID | package_weight | due_date | arr_date | running_sum_wht | Projection | Proj_date |
0 | 1 | 0 | 01-03-2021 | 01-03-2021 | 0 | ||
1 | 1 | 10 | 30-03-2021 | 15-03-2021 | 10 | 10 | 15-03-2021 |
2 | 1 | 20 | 15-04-2021 | 30 | 32 | 15-04-2021 | |
3 | 1 | 30 | 02-05-2021 | 60 | 44 | 02-05-2021 | |
4 | 1 | 40 | 30-06-2021 | 100 | 86 | 30-06-2021 |
package_no | client_ID | package_weight | due_date | arr_date | running_sum_wht | Projection | Proj_date |
0 | 1 | 0 | 01-03-2021 | 01-03-2021 | 0 | ||
1 | 1 | 10 | 30-03-2021 | 15-03-2021 | 10 | ||
2 | 1 | 20 | 15-04-2021 | 03-04-2021 | 30 | 30 | 03-04-2021 |
3 | 1 | 30 | 02-05-2021 | 60 | 65 | 02-05-2021 | |
4 | 1 | 40 | 30-06-2021 | 100 | 127 | 30-06-2021 |
package_no | client_ID | package_weight | due_date | arr_date | running_sum_wht | Projection | Proj_date |
0 | 1 | 0 | 01-03-2021 | 01-03-2021 | 0 | ||
1 | 1 | 10 | 30-03-2021 | 15-03-2021 | 10 | ||
2 | 1 | 20 | 15-04-2021 | 03-04-2021 | 30 | ||
3 | 1 | 30 | 02-05-2021 | 20-04-2021 | 60 | 60 | 20-04-2021 |
4 | 1 | 40 | 30-06-2021 | 100 | 214 | 30-06-2021 |
To create the projection here I got the values by simply calculating the slope for the line between the last and penultimate arrival dates and multiplying that by the difference in days between the corresponding next date and the period start date. The dates for each value is the corresponding due_date.
As you can see, the projection "advances" and gets updated each time a package arrives.
Thank you very much for your time, you're of great help.
Edit: added missing columns in the tables
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |