Project Current Trend into future days issue

[psmith-nhs-inc]

OK.  Concept is simple.  I have DAILYBURNRATEQTY (average sales QTY over previous three months) and I have CURRENTINVENTORYQTY.  Both are measures.  I have DAYSFROMTODAY, which is a calculated column in my DATE table, which goes out to the end of the year..

I want to an area chart going forward from TODAY into the future, on a daily scale,  CURRENTINVENTORYQTY - (DAILYBURNRATEQTY  * DAYSFROMTODAY)  So If I have 100 QTY, and a Burn rate of 1, I want to see days in the future from TODAY until the end of time according to my DATE table, and I should see 99, 98, 97...

My problem is that DAILYBURNRATEQTY is based on cumulative time based data and CURRENTINVENTORYQTY is based on summary data, but my future dates do not track anything, so there is no measure.  BI will not let me subtract a column value from a measure.  I need to get that future date into a measure of some kind, all by itself.  At least I think I do.  Other options are certainly welcome.

Thanx

Phil

[v-ljerr-msft]

Hi @psmith-nhs-inc,

Could you post your table structures(including the formula of the measures you're using) with some sample data and the expected result against it? So that we can better assist on this issue. It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[psmith-nhs-inc]

https://dl.dropboxusercontent.com/u/9936354/PurchasingDEVforum.pbix

Top Left: Daily Sales

Bottom Left:  Daily Burn rate, average daily sales based on selected slaes range.

Top Right Current Inventory on hand.

Bottom Right is mt current goal.

With a starting inventory of 100, display the date from today until the end of time as defined by my date table, that on hand inventory minus the cumulative Daily Burn Rate, so today shows 98, tomorrow shows 96, the day after shows 94, etc.

Phil