Forum Discussion
Date table column to show Dynamic Most-Recent entry date up until next entry date
Hello,
I would like help with this scenario I am not able to solve.
The data that I have is as follows:
Dates: (CALENDAR TABLE)
| ID | FiscalYear | Period |
| 1 | F1 | P1 |
| 2 | F1 | P2 |
| 3 | F1 | P3 |
| 4 | F1 | P4 |
| 5 | F1 | P5 |
| 6 | F1 | P6 |
| 7 | F1 | P7 |
Forecasts: (ONLY GET ENTERED EVERY n MONTHS)
| ID | Title | ForecastYear | ForecastPeriod |
| 1 | Data1 | F1 | P1 |
| 2 | Data2 | F1 | P3 |
| 3 | Data3 | F1 | P6 |
What I'd like to do is add a column to my Dates table that gets the latest Forecast Year & ForecastPeriod from the Forecasts table up to that FiscalYear & Period.
This way I can add a filter linked to my Dates table, and it would filter my Forecasts table to the most recent entry without going past the selected date in the filter.
i.e.
New Dates:
| ID | FiscalYear | Period | LatestForecastPeriod |
| 1 | F1 | P1 | P1 |
| 2 | F1 | P2 | P1 |
| 3 | F1 | P3 | P3 |
| 4 | F1 | P4 | P3 |
| 5 | F1 | P5 | P3 |
| 6 | F1 | P6 | P6 |
| 7 | F1 | P7 | P6 |
So with the filters, I could get a result such as the following:
FiscalYear Filter: F1
Period Filter: P4
| ID | Title | ForecastYear | ForecastPeriod |
| 2 | Data2 | F1 | P3 |
If data is setup the way you have shown above then you can do the following:
1: Go to Transfrom Data (Power Query)
2: In the Dates table select "Merge Queries" on Forecast Period on both tables. (as shown below)
3: Expand the merged query and select "Forecast Period":
4: Go to Transform tab on the top ribbon and select Fill Down
5: Select "Close and Apply". Now you have your desired output.
2 Replies
sm_talhaResolver II
If data is setup the way you have shown above then you can do the following:
1: Go to Transfrom Data (Power Query)
2: In the Dates table select "Merge Queries" on Forecast Period on both tables. (as shown below)
3: Expand the merged query and select "Forecast Period":
4: Go to Transform tab on the top ribbon and select Fill Down
5: Select "Close and Apply". Now you have your desired output.