Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calendar table with 13 periods

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

3 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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"

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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