Forum Discussion
Problem creating a line graph with running sums and a simple projection
Community SupportHi 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?
- Yes, that's it. In other words, I want the staright line betweem the penultimate package point and the last one to be projected forwards.
- Ideally I'd like the projection to have a point at each of the next due_dates. If that's too difficult, using only the last due_date is fine. If even that is too difficult, something simple like last arrival_date + 3 months will do well enough. The projection should be updated as packages arrive.
To illustrate this better I'll make some graphs. Again, blue is for due_date, orange is for arrival_date and red is for the projection. For each one assume current date to be one day after the last arrival_date. Also, there is a period_start date in another table that I now realized should be used as the starting "zero" point, which I used here to do the first projection.
This first one is just after package #1 arrives: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
Second one is just after package #2 arrives. In this one the projection exceeds the maximum of 100, that's intended: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
Third one is after package #3 arrives: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