cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlongano
Frequent Visitor

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)

IDFiscalYearPeriod
1F1P1
2F1P2
3F1P3
4F1P4
5F1P5
6F1P6
7F1P7

 

Forecasts: (ONLY GET ENTERED EVERY n MONTHS)

IDTitleForecastYearForecastPeriod
1Data1F1P1
2Data2F1P3
3Data3F1P6

 

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:

IDFiscalYearPeriodLatestForecastPeriod
1F1P1P1
2F1P2P1
3F1P3P3
4F1P4P3
5F1P5P3
6F1P6P6
7F1P7P6

 

So with the filters, I could get a result such as the following:

FiscalYear Filter: F1

Period Filter: P4

IDTitleForecastYearForecastPeriod
2Data2F1P3
1 ACCEPTED SOLUTION
sm_talha
Resolver II
Resolver 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)

 

sm_talha_0-1631052771497.png

 

3: Expand the merged query and select "Forecast Period": 

 

sm_talha_1-1631052834365.png

 

4: Go to Transform tab on the top ribbon and select Fill Down

 

sm_talha_2-1631052957806.png

 

5: Select "Close and Apply". Now you have your desired output. 

 

sm_talha_3-1631053077532.png

 

 

 

 

 

 

 

 

 

View solution in original post

2 REPLIES 2
sm_talha
Resolver II
Resolver 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)

 

sm_talha_0-1631052771497.png

 

3: Expand the merged query and select "Forecast Period": 

 

sm_talha_1-1631052834365.png

 

4: Go to Transform tab on the top ribbon and select Fill Down

 

sm_talha_2-1631052957806.png

 

5: Select "Close and Apply". Now you have your desired output. 

 

sm_talha_3-1631053077532.png

 

 

 

 

 

 

 

 

 

View solution in original post

Thank you so much! I was way overthinking it

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors