Fiscal Year To Date Flag

Question

My Fiscal year starts the 1st of October. I have the formula below for my YTD flag, but I would also like to create a Fiscal YTD Flag.
How do I do this?

= Table.AddColumn(#"Inserted Day of Year", "Flag_YTD", each if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate)

migasuke · Answer

Hi JC2022 ,Can you specify your expected outcome? What do you mean by YTD flag?&nbsp;

jc2022 · Answer

Migasuke&nbsp;For example:It is the 1st of April then every date &lt;= 1st of April gets "YTD" else "null".&nbsp; But this is for YTD. So 22nd of November gets a "null".I would like to create this for Fiscal YTD. My fiscal year starts 1st of October, so for Fiscal YTD flag it should return a "FYTD" for 22nd of November.

migasuke · Answer

Will you have more than 1 year?&nbsp;

jc2022 · Answer

Migasuke&nbsp;Absolutely. So 22nd of November in 2016 should have "FYTD" as a result but 22nd of November in 2018 should also have "FYTD" as a result. Even as 22nd of November current fiscal year (FY23) should have "FYTD" as a result.

migasuke · Answer

Hi JC2022 ,I think I solved your puzzle.1. First Create a column which will reflect "Today". You can create it with following function:DateTime.LocalNow()2. Change "Today" column to Date only (no Date-Time).3. Apply following code in custom column.if Date.AddMonths([Today],3) &gt;=Date.AddMonths([Date],3) and Date.Year(Date.AddMonths([Today],3))&gt;=Date.Year(Date.AddMonths([Date],3)) then "Flag" else nullLet me know if it helps.&nbsp;

Forum Discussion

Fiscal Year To Date Flag

8 Replies

Related content

Fiscal Year required

Year-to-Date Measure for all Fiscal Years?

Year to date cumulative

Fiscal Year and Fiscal Quarter

Fiscal Year to Date