Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |