Calendar table with 13 periods

[Anonymous]

Hi Everyone, thank you for the help in advance

I am having an issue with how to create a calendar table with 13 periods in a year, what would be the dax formula to say for eg. from my calendar date lists that IF(calendar date = 1/04/18 - 28/04/18 = Period 1 & IF (Calender date = 29/04/18 - 26/5/18) = PERIOD 2 ETC down to period 1. There are some periods with 29, 27 and 31 days in decemeber. What is the best way of achieving this and for multiples years, eg from 2015 to 2018.

Thanks 

Pangea

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You may need to create a calculated column like this:

period =
IF (
    'Calendar'[Date].[MonthNo] = 4
        && 'Calendar'[Date].[Day] >= 1
        && 'Calendar'[Date].[Day] <= 28,
    "Period1",
    IF (
        ( 'Calendar'[Date].[MonthNo] = 4
            && 'Calendar'[Date].[Day] >= 29 )
            || ( 'Calendar'[Date].[MonthNo] = 5
            && 'Calendar'[Date].[Day] <= 26 ),
        "Period2",
        IF (
            ( 'Calendar'[Date].[MonthNo] = 5
                && 'Calendar'[Date].[Day] >= 27 )
                || ( 'Calendar'[Date].[MonthNo] = 6
                && 'Calendar'[Date].[Day] <= 23 ),
            "Period3",
            "Other period"
        )
    )
)

Best regards,

Yuliana Gu

[Anonymous]

Thank you so much Yuliana and Chris for your extremely helpful replies

Yuliana - with the calculated column solution you gave me, am I able to to have an IF statement for the whole expression, so that it points to a particular fiscal year. E.G. if 2019 then ( your provided solution which includes the period dates) or IF 2018( your provided solution which includes slighty different period dates as when moving year to year the parameters will move a day forward) The periods are 28 days each roughly, begins on a sunday and ends on a saturday, e.g. period 1 in 2018 begins 1/4/18 and ends 28/4/18, however period 1 in 2017 begins 1/4/18 and ends 27/4/18. I hope this makes sense

Thanks in advance 

Pangea

[ChrisMendoza]

@Anonymous,

Possibly you could do something like the following in Power Query:

1.  Define your custom periods YYYY,MM,DD

2.  Add Index Column

3. Get Dates Between

4.  Expand Dates Between

5. Use MOD ( ) to define the 13 periods within 1 Cycle; the example breaks a calendar year into 12 periods with the custom range defined in '1.'

let
    Calendar = #table(
	{"PeriodStart", "PeriodEnd" },
	{
		{ #date ( 2017, 6, 1 ), #date ( 2017, 6, 30 ) },
		{ #date ( 2017, 7, 1 ), #date ( 2017, 8, 1 ) },
		{ #date ( 2017, 8, 2 ), #date ( 2017, 8, 31 ) },
		{ #date ( 2017, 9, 1 ), #date ( 2017, 9, 30 ) },
		{ #date ( 2017, 10, 1 ), #date ( 2017, 10, 31 ) },
		{ #date ( 2017, 11, 1 ), #date ( 2017, 11, 30 ) },
		{ #date ( 2017, 12, 1 ), #date ( 2017, 12, 31 ) },
		{ #date ( 2018, 1, 1 ), #date ( 2018, 1, 30 ) },
		{ #date ( 2018, 1, 31 ), #date ( 2018, 2, 28 ) },
		{ #date ( 2018, 3, 1 ), #date ( 2018, 3, 31 ) },
		{ #date ( 2018, 4, 1 ), #date ( 2018, 4, 30 ) },
		{ #date ( 2018, 5, 1 ), #date ( 2018, 5, 30 ) },
		{ #date ( 2018, 5, 31 ), #date ( 2018, 6, 30 ) },
		{ #date ( 2018, 7, 1 ), #date ( 2018, 7, 31 ) },
		{ #date ( 2018, 8, 1 ), #date ( 2018, 8, 30 ) },
		{ #date ( 2018, 8, 31 ), #date ( 2018, 9, 30 ) },
		{ #date ( 2018, 10, 1 ), #date ( 2018, 10, 30 ) },
		{ #date ( 2018, 10, 31 ), #date ( 2018, 11, 29 ) },
		{ #date ( 2018, 11, 30 ), #date ( 2018, 12, 31 ) },
		{ #date ( 2019, 1, 1 ), #date ( 2019, 1, 30 ) },
		{ #date ( 2019, 1, 31 ), #date ( 2019, 2, 28 ) },
		{ #date ( 2019, 3, 1 ), #date ( 2019, 3, 31 ) },
		{ #date ( 2019, 4, 1 ), #date ( 2019, 4, 30 ) },
		{ #date ( 2019, 5, 1 ), #date ( 2019, 5, 30 ) },
		{ #date ( 2019, 5, 31 ), #date ( 2019, 6, 30 ) }
	}
),
    #"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1),
    #"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ),
    #"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ),
    #"Removed PeriodStartPeriodEnd" = Table.RemoveColumns ( #"Expanded DatesBetween", { "PeriodStart", "PeriodEnd" } ),
    #"Changed Type To Date" = Table.TransformColumnTypes(#"Removed PeriodStartPeriodEnd",{{"Date", type date}}),
    #"Added PeriodNum" = Table.AddColumn(#"Changed Type To Date", "PeriodNum", each if Number.Mod ( [PeriodIndex], 12 ) = 1 then 12 else Number.Mod ( [PeriodIndex] - 1, 12 ))
in
    #"Added PeriodNum"