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

Create Current Backlog database to chart Year over Year

Hi all, 

I am looking to create a year over year backlog database that I can graph and forecast. I have been working on it for a while but I can't seem to get it exactly where I want it with accurate numbers. This was originally created in Excel but it was complicated and outdated and I need to create a new version to make it more visual friendly so I would like to be able to put in on graphs and charts. I would like to be able to chart the total in each month and have that go back a few years to see where we have slower shipping times or where we have the highest backlog totals. 

So the problem, say I have 2 dates, the request date and the actual ship date. If the order is past the request date then in backlog the price should show up, assuming the order has not been shipped already. If the request date is later than today, then the price should be put in future backlog, which means for us that the order is not late but this is how much in $$$ that is waiting to be shipped. 

Another thing- I would like to be able to change the date to any day. I have created a calendar table that works and I also created a slicer to to reference that in dax formulas. 

Right now my nonworking Backlog formula is: 

Backlog= IF( AND('Data'[Actual Ship Date]>= [Slicer Date], 'Data'[Request Date]< [Slicer Date]), 'Data'[Price], 0)

Future Backlog = IF(AND('Data'[Order Date]<[Slicer Date], AND('Data'[Request Date]> [Slicer Value], 'Data'[Actual Ship Date]>= [Slicer Date])), 'Data'[Ext Price], 0)
 

The problems I am having: I am not getting the right total for the backllog, I would like to be able to change the slicer value(whatever date that I would like to display) to get the backlog price at that exact time. For example if I wanted to know what the backlog total amount was on March 1st, 2019, it would only show as 22,000.  On March 1st, 2019, there would also be 6,000 in Future Backlog because there was an order placed on that date. The two orders placed 5/5/2019 and 6/25/2019 would not be included in future backlog because the order date is after the slicer date. Lastly, the slicer date is not dynamic and I do not know if that can be changed because any date I choose, the totals in powerbi do not change, it just filters out anything after the date I choose. 

 

Order Date

Request Date

Actual Ship Date

Price

Backlog Price

Future Backlog Price

12/15/2017

1/1/2018

 

10,000

10,000

0

1/15/2018

3/5/2018

 

12,000

12,000

0

9/1/2018

1/15/2019

1/10/2019

5,000

0

0

1/10/2019

2/10/2019

2/10/2019

8,000

0

0

3/1/2019

3/20/2019

 

6,000

6,000

0

5/5/2019

6/15/2019

6/5/2019

14,000

0

0

5/5/2019

7/1/2019

 

10,000

0

10,000

6/25/2019

8/15/2019

 

15,000

0

15,000

 

Reference Date

Total Price

Current Backlog

Total Future Backlog

6/20/2019

55,000

28,000

25,000

 

Thank you so much if you have suggestions, I am just lost. If there is another/ better way to do this I would love to hear it, I am just so sick of putting in dates that don't give me what I need. Thanks again!

 

 @jdbuchanan71 @Zubair_Muhammad @Sean  @tex628  @MFelix  @Greg_Deckler 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Create Current Backlog database to chart Year over Year

@kwats005 

 

Values in a calculated column are fixed. They are an immutable result for each row in the table. You may use SUMX to create a measure.

https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
kwats005
Frequent Visitor

Re: Create Current Backlog database to chart Year over Year

Capture.JPG

 

I would like to be able to chart something like this, to show the cycles of when there is a higher current backlog or future backlog at a certain month. Thank you!

Community Support
Community Support

Re: Create Current Backlog database to chart Year over Year

@kwats005 

 

Values in a calculated column are fixed. They are an immutable result for each row in the table. You may use SUMX to create a measure.

https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors