Forum Discussion
Cumulative backlog to date
andrew_hardwick Couple small changes since there are blank dates... But you should be set now! Let me know.
andrew_hardwick Can you post what results you expect for the data sample you've provided on the bottom?
Opened Resolved Balance
April 8, 2016 1 0 1
April 14, 2016 3 0 4
April 15, 2016 1 0 5
April 18, 2016 7 8 4
April 19, 2016 0 2 2
May 4, 2016 0 1 1
May 6, 2016 0 1 0
Also do you have a Calendar Table - you will need one!
andrew_hardwick You will need a Calendar Table for this one.
Relate both Opened and Resolved Dates from your table to Date in the Calendar Table
Then the Measures are pretty straightforward. Let me know if you have any questions!
Hi Sean
No, I do not have a Calendar Table, so I suspect this is where I am going wrong. Do you know where I find one, or how to create one?
PS, I really am new to this :-)
Thanks,
Andy
Here's a link on how to create a Calendar table
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
Then just set up the relationships and create the measures as in my picture above.
Hope this helps. Let me know if you have any other questions.
Thats great. thanks for that, I now have a Calendar. I have built my report using the measures you stated and the Calendar and it is now almost there, I have the balance and RT Balance showing. The ony remaining issue I see is that some of the lines do not yet have a resolved date, they are still being worked on. This therefore gives me a negative RT Balance at the start as their is no date assigned. Instead I would like this open entries to appear on the latest RT Balance day, i.e. today.
Any idea how I could achieve this?
