Solved: day of fiscal year column

JC2022 · ‎04-01-2023

I would like to create a day of fiscal year column.
My fiscal year starts the 1st of October. I have the date in a date column like 1-10-2016 and this should return value 1 which it does. The first of January 2017 should return value 93 but is returning -272.
Please see my power query formula below and help me out.

= Table.AddColumn(#"Changed Type", "Day of Fiscal Year", each Duration.Days([Date] - #date(Date.Year([Date]),10,1)) + 1)

CNENFRNL · ‎04-01-2023

let
    Source = Table.FromList(List.Dates(#date(2022,9,20),40,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
    #"Day of FY" = Table.AddColumn(Source, "Day of FY", each Duration.Days([Date] - #date(Date.Year([Date])-Byte.From(Date.Month([Date])<10),10,1))+1)
in
    #"Day of FY"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

CNENFRNL · ‎04-01-2023

let
    Source = Table.FromList(List.Dates(#date(2022,9,20),40,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
    #"Day of FY" = Table.AddColumn(Source, "Day of FY", each Duration.Days([Date] - #date(Date.Year([Date])-Byte.From(Date.Month([Date])<10),10,1))+1)
in
    #"Day of FY"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!