Fiscal Year Offset

Power Query
1

Hi All,

Looking for some help on setting up some offsets. I have managed to create a calculated column in DAX for the month and week offset, but not sure how to do this for the fiscal year. Ideally I would like to have all of this within my M code.

I am working with a non-standard date table where the Financial year starts differently every year. Any help is appreciated.

Sample PBIX file EnterpriseDNA.pbix (2.4 MB)

2

Hi Nurry90,

Please try attached M Code.Calendar.txt (4.2 KB)

3

Hi @Nurry90,

To protect the logic make sure today’s date is present

image
image877×48 4.9 KB

Determine the values for the Current date

image
image737×81 4.13 KB

Replicate your FY logic and remove today if it was added

image
image992×66 8.01 KB

.

Here’s your sample file. Sample PBIX file EnterpriseDNA (1).pbix (2.4 MB)
I hope this is helpful.

4

Hi @Melissa,

Thank you for your help with this :slight_smile:

Just wondering, is it the same logic to include the Month Offset and the Week offset into my M code also? I’ve just realised my DAX Month offset is not working for the month after where there is a week 53 so I need to use my M code logic.

Sample PBIX file EnterpriseDNA.pbix (2.4 MB)

image

5

Hi @Nurry90,

Yes you are right, the process is the same. Fix your logic first in the language your most comfortabele with (I presume DAX) then transfer it to M leveraging the same technique as used for the FY Offset

6

Hi @Nurry90, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

Hi @Nurry90, we’ve noticed that no response has been received from you since the 26th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi Melissa,

Struggling with adding the month offset into my M code. The below is the Dax version, but not sure what to do in M. Can you help talk me through it?

RelativeMonthOffset =
VAR maxdate = MAX(‘Sellthrough Files’[Load Date])
return
IF(‘Date Table’[WkStartMonth]>=7,
(12YEAR(‘Date Table’[Wk Beginning]) + MONTH(‘Date Table’[Wk Beginning])) - (12YEAR(maxdate) + MONTH(maxdate)),
IF(‘Date Table’[WkStartMonth]= 6 && ‘Date Table’[FY Week number] = 1,
(12YEAR(‘Date Table’[Wk Beginning]) + MONTH(‘Date Table’[Wk Beginning])+1) - (12YEAR(maxdate) + MONTH(maxdate)),
IF(‘Date Table’[WkStartMonth]= 6 && ‘Date Table’[FY Week number] = 53,
(12YEAR(‘Date Table’[Wk Beginning]) + MONTH(‘Date Table’[Wk Beginning])) - (12YEAR(maxdate) + MONTH(maxdate)),
IF(‘Date Table’[WkStartMonth] <= 6,
(12YEAR(‘Date Table’[Wk Beginning]) + MONTH(‘Date Table’[Wk Beginning])) - (12YEAR(maxdate) + MONTH(maxdate)),
BLANK()))))

9

Hi @Nurry90,

There is still a problem with your DAX logic for the FM Offset calculation and also with your PQ M “Fiscal Month & FY” asuming your MonthID is correct…

image
image547×681 11.7 KB

.

I recomment you do some thorough analysis on all your Fiscal date attributes, before you continue. However here’s the PQ M Offset logic that aligns with your Fiscal Year and MonthID

  AddFMoffset = Table.AddColumn(AddFYOffset, "FiscalMonthOffset", each
    ((12* [Fin Yr]) +
    (if [WkStartMonth]>=7 then [WkStartMonth]-6 else
      ( if ([WkStartMonth]=6 and [FY Week number]=1 ) then [WkStartMonth]-5 else
      ( if ([WkStartMonth]=6 and [FY Week number]=53) then [WkStartMonth]-6 else 
      [WkStartMonth] +6)))) -
    ((12* CurrentFY) + CurrentMID), type number )

.

Your file. Sample PBIX file EnterpriseDNA (1).pbix (2.4 MB)
Since your initial query has been answered please mark this thread as Solved.

I hope this is helpful.

10

Hi Melissa,

Thank you for your help, as always. Much appreciated!

1 Like