cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DusanVH
Frequent Visitor

Creating a line graph with running sums and a forecast

 Hi, firstly I'll leave some example data:

 

package_IDclient_IDpackage_weightdue_datearrival_date
112003-03-202105-03-2021
211508-04-202102-04-2021
321808-03-202112-03-2021
432305-03-202103-03-2021
513520-05-2021 
631210-04-202131-03-2021
72813-04-202120-04-2021
833308-06-2021 
912610-06-2021 
1021409-06-2021 
1126008-07-2021 
1233215-07-2021 
131420-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.

 

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:

 

DusanVH_0-1624590891501.png

 

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.

 

Also, apparently the forecast functionality is of no help in this case because the dates are not at regular intervals.

 

Any help is appreciated!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@DusanVH ,with help from a date table which joined to both dates assume due date join is inactive try measure like

 

Cumm arrival= CALCULATE(SUM(Table[package_weight]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm due = CALCULATE(CALCULATE(SUM(Table[package_weight]), userelationship(date[date],table[due_date]) ),filter(allselected(date),date[date] <=max(date[Date])))

 

if needed add filter for not(isblank([arrival date])) or not(isblank([due date])) in respective formula



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@DusanVH ,with help from a date table which joined to both dates assume due date join is inactive try measure like

 

Cumm arrival= CALCULATE(SUM(Table[package_weight]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm due = CALCULATE(CALCULATE(SUM(Table[package_weight]), userelationship(date[date],table[due_date]) ),filter(allselected(date),date[date] <=max(date[Date])))

 

if needed add filter for not(isblank([arrival date])) or not(isblank([due date])) in respective formula



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Sorry for the late follow-up.

Your suggestion worked for me, although with a change:

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)))
But then it doesn't work for cumm arrival. Here's my code:
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)))
Here's an example graph with different data but same logic of what I see:
DusanVH_0-1625690316216.png

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 should'nt work, as I should use the date[date] instead, but when I do, the table is empty. I imagine this is due to me not using the dates table in my code. I also used different code because if I used the exact one you provided, the Cumm arrival line went to a constant 100, the arrival date points started at the first date of my Dates table to the last one, and there was one arrival date point for each day, like so:

DusanVH_1-1625693536215.png

 

I'm also missing the projection:

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.

 

Thanks in advance for your time, your help is appreciated.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!