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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

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