My Date Table is populating the wrong information

I copied and pasted the Date text file and entered it into the query editor as instructed in the video. The start month is supposed to be January 01/01/2017. The FY shows 18. And the start date shows 07.01.2017



@Hermann007

You can just do a Replace FY18 with FY17, then Replace FY19 with FY18, and so forth (do the same for ShortYear) or go into the Advanced Editor for the Date Query and remove the +1 in the last row and create a new date table. Melissa has more complete M code suggestion and I’ve tried it and found that it works well.

1 Like

Ok go to the advanced editor and change the M code slightly, top lines:

    let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number ) as table =>
      let
        FYStartMonth = try if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1 otherwise 1,
        DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,

and also replace the last lines.

AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if FYStartMonth =1 then Text.End(Text.From([Year]), 2) else if [MonthOfYear]>=FYStartMonth then Text.From(Number.From(Text.End(Text.From([Year]), 2))+1) else Text.End(Text.From([Year]), 2)), type text)
in
    AddFY
in
    fnDateTable

.
If your FY starts in Jan, you can omit the last parameter.
I hope this is helpful.

2 Likes

Thanks @Melissa. That seems to have fixed the issue. Any idea why this may have been an issue in the first place?

Thx for taking the time to answer my question @powerbideveloper

Sure, it looks like the M code wasn’t designed for FYs starting in January kinda logical if you think about it because there won’t be a difference between the [Year] and the [FY] in your calendar.
As @powerbideveloper has already indicated when it encounters a [Date] with a month number greater than or equal to the FYStartMonth it adds one to the ShortYear. Now that makes perfect sense for every other FYStartMonth than January. :wink:

So as a quick fix, made the FYStartMonth optional and accounted for January…

It’s great to know that the issue has been fixed @Hermann007. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!