Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jramirej
Regular Visitor

Need to calculate if rows belong to last three fiscal year in Power Query

Hi Team, 

I have DIM_DATE table which contains Full_Date, Fiscal_Year_Start_Date, Fiscal_Year_End_Date, Today_Date, Fiscal_Year_Number and Calendar_Year. Current fiscal year is not available in the Table.

Jramirej_0-1743500031912.png

 

The Fiscal Year start and end are as per Fiscal_Year_Start_Date and Fiscal_Year_End_Date. Fiscal Year start and end is not specified by any fixed date range like 1st Jan or 4th Apr each year.
Fiscal_Year_Number contains values from 2020 to 2027. In Power Query I need to create a column for which rows are in last three fiscal year. Current fiscal year is 2026, so last three fiscal year should be 2024,2025,2026. If rows in last three fiscal year then Yes else No. Current fiscal year should not hard coded, this should be dynamic calculation for every year. 

Need solution in Power Query

Please find the dataset https://docs.google.com/spreadsheets/d/1fnHBzbyOps2aVe00EvSAMOxqIvsv3caU/edit?usp=sharing&ouid=10994... 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Jramirej,

 

Here is my M code

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-vpabbu\Desktop\BI Reports\Need to calculate.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Full_Date", type date}, 
        {"Fiscal_Year_Start_Date", type date}, 
        {"Fiscal_Year_End_Date", type date}, 
        {"Fiscal_Year_Number", Int64.Type}, 
        {"Cal_Year_Number", Int64.Type}, 
        {"Today_Date", type date}, 
        {"If last 3 fiscal Year Yes, else No", type any}, 
        {"Column8", type any}
    }),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column8", "If last 3 fiscal Year Yes, else No"}),
    MaxFiscalYear = List.Max(#"Removed Columns"[Fiscal_Year_Number]),
    #"Added Custom Column" = Table.AddColumn(#"Removed Columns", "If last 3 fiscal Year Yes, else No", each if [Fiscal_Year_Number] >= MaxFiscalYear - 2 then "Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom Column", each true)
in
    #"Filtered Rows"


Output:

vvpabbu_0-1743764875429.png

 

Regards,

Vinay Pabbu

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @Jramirej,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

 

Regards,
Vinay Pabbu

Anonymous
Not applicable

Hi @Jramirej,

 

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

 

Regards,
Vinay Pabbu

Anonymous
Not applicable

Hi @Jramirej,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

 

Regards,
Vinay Pabbu

lbendlin
Super User
Super User

1. Why does your Fiscal_Year_Start_Date keep increasing each day?

2. These values are immutable. No need to do this in Power Query.  Use an external reference table.

 

this should be dynamic calculation for every year

Just because you can automate things doesn't mean you should.  Year End Close may require the "Last FY" to stay around a bit longer.

Hi @lbendlin 

Yes this dynamic calculation needed. 
I have updated the excel sheet after corrected Fiscal_Year_Start_Date

Please find the dataset https://docs.google.com/spreadsheets/d/1fnHBzbyOps2aVe00EvSAMOxqIvsv3caU/edit?usp=sharing&ouid=10994... 

Anonymous
Not applicable

Hi @Jramirej,

 

Here is my M code

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-vpabbu\Desktop\BI Reports\Need to calculate.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Full_Date", type date}, 
        {"Fiscal_Year_Start_Date", type date}, 
        {"Fiscal_Year_End_Date", type date}, 
        {"Fiscal_Year_Number", Int64.Type}, 
        {"Cal_Year_Number", Int64.Type}, 
        {"Today_Date", type date}, 
        {"If last 3 fiscal Year Yes, else No", type any}, 
        {"Column8", type any}
    }),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column8", "If last 3 fiscal Year Yes, else No"}),
    MaxFiscalYear = List.Max(#"Removed Columns"[Fiscal_Year_Number]),
    #"Added Custom Column" = Table.AddColumn(#"Removed Columns", "If last 3 fiscal Year Yes, else No", each if [Fiscal_Year_Number] >= MaxFiscalYear - 2 then "Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom Column", each true)
in
    #"Filtered Rows"


Output:

vvpabbu_0-1743764875429.png

 

Regards,

Vinay Pabbu

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors