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

[Jramirej]

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.

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... 

[Anonymous]

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]

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]

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]

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.

[Jramirej]

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 · Accepted solution]

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:

Regards,

Vinay Pabbu