Company Financial Period, DAX or M Code?

Power Query
1

Hi,

I am currently using the excellent date table M code from the resources to create my date table in my Data model.

My company is based on a Fiscal Period of ‘Period 1 to Period 13’ which starts every September of each year.

Period 1, Week 1,2,3,4 and then Period 2 Etc. etc… Week Commencing is always Sunday Dated.

Please see attached excel screenshot.

Period Dates
Period Dates1021×544 139 KB

I would like to incorporate this into my Lookup Date Table.

What’s the best approach? DAX or an M Code workaround? (Tweaking the M Code)

I am currently using a Switch(True) solution in a calculated column within my Date Table to achieve this. - My code is below for my Calculated Column ‘Period’ (Period 8 to 13)

My question is? Is there a better way of obtaining the same solution? (I’m still quite new to DAX)

Period =

SWITCH (
TRUE (),
AND (
‘DIM Dates Table’[Date] >= DATE ( 2020, 4, 19 ),
‘DIM Dates Table’[Date] <= DATE ( 2020, 5, 9 )
),
8,
AND (
‘DIM Dates Table’[Date] >= DATE ( 2020, 5, 10 ),
‘DIM Dates Table’[Date] <= DATE ( 2020, 6, 6 )
),
9,
AND (
‘DIM Dates Table’[Date] >= DATE ( 2020, 6, 7 ),
‘DIM Dates Table’[Date] <= DATE ( 2020, 7, 4 )
),
10,
AND (
‘DIM Dates Table’[Date] >= DATE ( 2020, 7, 5 ),
‘DIM Dates Table’[Date] <= DATE ( 2020,8, 01 )
),
11,
AND (
‘DIM Dates Table’[Date] >= DATE ( 2020, 8, 2 ),
‘DIM Dates Table’[Date] <= DATE ( 2020, 8, 29 )
),
12,
13
)

Thanks Darren

2

I see you’re hard coding dates in your current DAX approach. Can you share the logic for determining the first day for each 1st fiscal period? I figure you must have a correction somewhere because 4 x 13 = 52 weeks or 364 days in each year…

3

Hi Melissa,

Thanks for replying and assisting myself.

As we are 3 quarters into the Period Calendar and I am only just learning all the new great Dax techniques.

For now as a shortcut, I used just Switch Logic for the remaining Calculated Periods (Not from the start of the Fiscal Year)

I get your point though, I need to move away from Hard Coding dates and probably focus on setting a Start of year in my DAX logic (Variable Maybe?)

I’m not that experienced yet but I get what you are saying and appreciate your thoughts.

Thanks
Darren

Model Relationship
Model Relationship1000×642 35.8 KB
Period Calculated Column V2
Period Calculated Column V21485×735 276 KB
Period Calculated Column
Period Calculated Column1583×819 129 KB

4

You could at least start with using variables, I have modified your code a bit, making it easier to read and it should be a bit more efficient.

Period =
VAR MyDate = 'DIM Dates Table'[Date]
VAR MyYear = 2020
VAR Result =
    SWITCH (
        TRUE (),
        MyDate >= DATE ( MyYear, 4, 19 )
            && MyDate <= DATE ( MyYear, 5, 9 ), 8,
        MyDate >= DATE ( MyYear, 5, 10 )
            && MyDate <= DATE ( MyYear, 6, 6 ), 9,
        MyDate >= DATE ( MyYear, 6, 7 )
            && MyDate <= DATE ( MyYear, 7, 4 ), 10,
        MyDate >= DATE ( MyYear, 7, 5 )
            && MyDate <= DATE ( MyYear, 8, 1 ), 11,
        MyDate >= DATE ( MyYear, 8, 2 )
            && MyDate <= DATE ( MyYear, 8, 29 ), 12,
        13
    )
RETURN
    Result
5

Hi Both,

Many thanks for your help and suggestions.

I have adopted the cleaner code as suggested by AntrikshSharma.

Reset the date in the M Code parameter to run for the full Company ‘Fiscal year’
29/9/2019 - 26/9/2020

Added and amended the new cleaner Dax code and all is good except for the cross over from 2019 to 2020 for Period 4, my Switch code is ignoring/missing Period 4.

Tried altering the Variable for MyYear slightly to catch both years (2019 & 2020)
Looks like I’m still wrong in my logic somewhere :thinking: :thinking:

Period =
VAR MyDate = ‘DIM Dates Table’[Date]
VAR MyYear = IF(AND(‘DIM Dates Table’[Date]<=DATE(2019,12,31), ‘DIM Dates Table’[Year]=“2019”),2019,2020)
VAR Result =
SWITCH (
TRUE (),
MyDate >= DATE ( MyYear, 9, 29 )
&& MyDate <= DATE ( MyYear, 10, 26 ),1,
MyDate >= DATE ( MyYear, 10, 27 )
&& MyDate <= DATE ( MyYear,11, 23 ), 2,
MyDate >= DATE ( MyYear, 11, 24 )
&& MyDate <= DATE ( MyYear, 12, 21 ), 3,
MyDate >= DATE ( MyYear, 12, 22 )
&& MyDate <= DATE ( MyYear, 1, 18 ), 4,
MyDate >= DATE ( MyYear, 1, 19 )
&& MyDate <= DATE ( MyYear, 2, 15 ), 5,
MyDate >= DATE ( MyYear, 2, 16 )
&& MyDate <= DATE ( MyYear, 3, 14 ), 6,
MyDate >= DATE ( MyYear, 3, 15 )
&& MyDate <= DATE ( MyYear, 4, 11 ), 7,
MyDate >= DATE ( MyYear, 4, 12 )
&& MyDate <= DATE ( MyYear, 5, 9 ), 8,
MyDate >= DATE ( MyYear, 5, 10 )
&& MyDate <= DATE ( MyYear, 6, 6 ), 9,
MyDate >= DATE ( MyYear, 6, 7 )
&& MyDate <= DATE ( MyYear, 7, 4 ), 10,
MyDate >= DATE ( MyYear, 7, 5 )
&& MyDate <= DATE ( MyYear, 8, 1 ), 11,
MyDate >= DATE ( MyYear, 8, 2 )
&& MyDate <= DATE ( MyYear, 8, 29 ), 12,
13
)
RETURN
Result

image
image1579×817 92.1 KB
Pick and Despatch All Bakeries(Limited Data Version.pbix (199.5 KB)

6

Try something like this: also you could change that MyYear part to this

IF ( MyDate <= DATE ( 2019, 12, 31 ) && YEAR ( MyDate ) = 2019, 2019, 2020 )

image
image974×500 38 KB

7

That Code worked flawlessly - Thanks for your help

1 to 13 now showing up correctly

Thanks for taking time to answer my first question on this amazing Forum!

Period =
VAR MyDate = ‘DIM Dates Table’[Date]
VAR MyYear = 2019
VAR Result =
SWITCH (
TRUE (),
MyDate >= DATE ( MyYear, 9, 29 )
&& MyDate <= DATE ( MyYear, 10, 26 ),1,
MyDate >= DATE ( MyYear, 10, 27 )
&& MyDate <= DATE ( MyYear,11, 23 ), 2,
MyDate >= DATE ( MyYear, 11, 24 )
&& MyDate <= DATE ( MyYear, 12, 21 ), 3,
MyDate >= DATE ( MyYear, 12, 22 )
&& MyDate <= DATE ( MyYear +1, 1, 18 ), 4,
MyDate >= DATE ( MyYear + 1, 1, 19 )
&& MyDate <= DATE ( MyYear +1 , 2, 15 ), 5,
MyDate >= DATE ( MyYear + 1, 2, 16 )
&& MyDate <= DATE ( MyYear + 1, 3, 14 ), 6,
MyDate >= DATE ( MyYear + 1, 3, 15 )
&& MyDate <= DATE ( MyYear + 1, 4, 11 ), 7,
MyDate >= DATE ( MyYear + 1, 4, 12 )
&& MyDate <= DATE ( MyYear + 1, 5, 9 ), 8,
MyDate >= DATE ( MyYear + 1, 5, 10 )
&& MyDate <= DATE ( MyYear + 1, 6, 6 ), 9,
MyDate >= DATE ( MyYear + 1, 6, 7 )
&& MyDate <= DATE ( MyYear + 1, 7, 4 ), 10,
MyDate >= DATE ( MyYear + 1, 7, 5 )
&& MyDate <= DATE (MyYear + 1, 8, 1 ), 11,
MyDate >= DATE ( MyYear + 1, 8, 2 )
&& MyDate <= DATE ( MyYear + 1, 8, 29 ), 12,
13
)
RETURN
Result

image
image1585×783 98.8 KB

1 Like
8

Awesome! You’re welcome!

1 Like
9

Hi @DarrenG,

Wanted to share this M code for a 13 Period Date table with you.

let
    FYStartDate = #date(2019, 9, 29), // Enter your FY StartDate here
    StartDate = Number.From( FYStartDate ),
    Years = Date.Year( DateTimeZone.FixedUtcNow() ) -  Date.Year( FYStartDate ) +1,
    EndDate = StartDate + ( 364 * Years ),
    ListDates = {StartDate..EndDate},
    ConvertedToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "DateKey"}}),
    DateKey = Table.TransformColumnTypes(RenamedColumns,{{"DateKey", type date}}),
    DayID = Table.AddIndexColumn(DateKey, "DayID", 1, 1, Int64.Type),
    YearID = Table.AddColumn(DayID, "YearID", each Number.RoundDown(([DayID]-1)/364)+1, Int64.Type),
    //QuarterID = Table.AddColumn(YearID, "QuarterID", each Number.RoundDown(([DayID]-1)/91)+1, Int64.Type),
    MonthID = Table.AddColumn(YearID, "MonthID", each Number.RoundDown(([DayID]-1)/28)+1, Int64.Type),
    WeekID = Table.AddColumn(MonthID, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1, Int64.Type),
    StartOfWeek = Table.AddColumn(WeekID, "Start of Week", each Date.StartOfWeek([DateKey], Day.Sunday), type date),
    EndOfWeek = Table.AddColumn(StartOfWeek, "End of Week", each Date.EndOfWeek([DateKey], Day.Sunday), type date),
    InsertYear = Table.AddColumn(EndOfWeek, "FY Year", each Date.Year( FYStartDate ) -1 + [YearID], Int64.Type ),
    InsertPeriod = Table.AddColumn(InsertYear, "FY Period", each [MonthID] - (( [YearID] -1 ) * 13 ), Int64.Type ),
    InsertWeek = Table.AddColumn(InsertPeriod, "FY Week", each [WeekID] - (( [YearID] -1 ) * 52 ), Int64.Type),
    InsertDay = Table.AddColumn(InsertWeek, "FY Day", each [DayID] - (( [YearID] -1 ) * 364 ), Int64.Type)
in
    InsertDay

.
It’s based on this article.

I hope you find it useful.

1 Like
10

That’s Brilliant Melissa!

Many thanks for creating and sharing the M Code

Cheers Darren