Forum Discussion

Morton041740's avatar
Morton041740
Frequent Visitor
7 years ago
Solved

DATEADD contiguous date error

I am working with a stock inventory report and would like to add a what if parameter to allow the report user to pick a number of days by which they change the stock expiry date either positive or negative and the results of this would be displayed on a graph showing when the stock would be unavailable due to expiry. Ultimately, I want to combine this with a sales forecast chart to plot stock expiry against projected sales with a percentage what if parameter on the sales forecast and a number of days what if parameter to adjust the expiry dates.

This is my chart so far with forecast sales and actual expiry dates. I have the percentage sales difference working but am hitting some problems with the expiry date what if parameter.

 

 

 

 

 

 

 

 

 

 

 

I have a calendar table available and a stock table with stock availability and expiry date columns. The model has a disabled relationship between the expiry date and the Calendar table. I was trying to find the number of days to expiry from the current date using a what if parameter to add or subtract days from the actual expiry date using the following formula. 

 

What If days to expiry = CALCULATE(DATEADD('Calendar'[Date], 'Change in Expiry Date'[Change in Expiry Date Value], DAY) - TODAY(),USERELATIONSHIP('StockPriceData'[Expiry Date], 'Calendar'[Date]),ALL('Calendar'))

 

When I include the measure in a table it throws an error saying that the DATEADD function needs contiguous data. I was hoping that the use of the Calendar table would get around this issue but it is failing.

Is anyone able to help guide me on a way to achieve the what if scenario on the expiry dates?

 

  • Anonymous's avatar
    Anonymous
    7 years ago

    Morton041740 -

    A couple troubleshooting possibilities:

    1. Does the calendar table have any gaps?

    2. Try using expiry date as the Enabled relationship and see whether a simpler version of the measure works.

    Hope this helps,

    Nathan

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Morton041740 -

    A couple troubleshooting possibilities:

    1. Does the calendar table have any gaps?

    2. Try using expiry date as the Enabled relationship and see whether a simpler version of the measure works.

    Hope this helps,

    Nathan