Forecast data display

[Rakesh_508]

Hello,

I have a work order table which has work order ids, date reported,status, etc. On basis of that created a forecast table for next 3 years.

I need to show the goal i.e., through button drillthrough, I had merged boh current and forecast tables into a single one.

I created a date table i.e., with min of date reported from current table and max of next date reported column from the forecast table.

created a date slicer based on date table and values of date slicer are current year, next year, curret quarter like that...

If I select current year , the goal is showing only current year data upto current work orders and forecast data in the current year...

The requirement is if i select current year, along with current year work order , it should display current year forecast and along with next 2 years forecast data for the particular work orders  i.e, along with 2024, next two years 2025 and 2026 forcast data also should show...

Please help me on this..

Thanks

[johnDurr]

To meet your requirement, follow these steps:

1. **Create a Date Slicer
Ensure the date slicer uses the Date table which includes the full range from the minimum date in the current table to the maximum date in the forecast table.

2. Adjust the Date Slicer Logic
Create a measure to handle the logic of displaying data for the current year and the next two years when the current year is selected.

3. Create a Custom Measure
Use a DAX measure to check if the selected date is in the current year and then include the forecast for the next two years.

```DAX
SelectedYear = YEAR(TODAY())
DisplayData =
IF (
YEAR(MAX(DateTable[Date])) = SelectedYear,
CALCULATE (
SUM(MergedTable[Value]),
YEAR(MergedTable[Date]) IN {SelectedYear, SelectedYear + 1, SelectedYear + 2}
),
SUM(MergedTable[Value])
)
```

4. Use the Measure in Your Visualization
Replace the existing measure in your visuals with `DisplayData`.

5. Test the Slicer
Ensure that selecting the current year in the slicer shows data for the current year along with the forecast for the next two years.