I would like to create a custom range for dates based on a 13 Period Calendar

[Anonymous]

I would like to create a custom 13 Period Calendar.  I have created a date table in power BI, and for example I would like to include the range of January 1 - January 28 as Period 1, January 29 - February 25 as period 2 and so on.  I have multiple years in my Power BI and the 13 period calendar would be consistent for every year in my date calendar.

[evandrocunico]

Hi @Anonymous 

Create a column:

period = if(MONTH('calendar'[Date])=1;if(day('calendar'[Date])<29;1;(month('calendar'[Date])+1)); If (MONTH('calendar'[Date])>=1&&day('calendar'[Date])>25;(month('calendar'[Date])+1);if(MONTH('calendar'[Date])>=2&&day('calendar'[Date])<=25;month('calendar'[Date]))))

regards.

[Anonymous]

This worked.  Thank you very much!!

[v-chuncz-msft]

@Anonymous 

By the way, you may help accept solution. Your contribution is highly appreciated.

[Anonymous]

Hello, as a follow-up I have modified the formula to fit my tables however the logic is assuming the 25th is the end of all my periods.

The 13 periods are as follows:

1) 1/1-1/28

2) 1/29-2/25

3) 2/26-3/25

4) 3/26-4/22

5) 4/23-5/20

6) 5/21-6/17

7) 6/18-7/15

😎 7/16-8/12

9) 8/13-9/9

10) 9/10-10/7

11) 10/8-11/4

12) 11/5-12/2

13) 12/3-12/31

Are you able to help me with the revised formula to match the periods above?  The dates would be for any year in the calendar.  This is my current formula and table references:

Period # = if(MONTH('Dates'[Date])=1,if(day('Dates'[Date])<29,1,(month('Dates'[Date])+1)), If (MONTH('Dates'[Date])>=1&&day('Dates'[Date])>25,(month('Dates'[Date])+1),if(MONTH('Dates'[Date])>=2&&day('Dates'[Date])<=25,month('Dates'[Date]))))

Thank you!

[evandrocunico · Accepted solution]

@Anonymous 

Period # =
if(MONTH('Calendario'[Data])=1&&day('Calendario'[Data])>=1&&day('Calendario'[Data])<=28,1,
if(MONTH('Calendario'[Data])=1&&day('Calendario'[Data])>=29||MONTH('Calendario'[Data])=2&&day('Calendario'[Data])<=25,2,
if(MONTH('Calendario'[Data])=2&&day('Calendario'[Data])>=26||MONTH('Calendario'[Data])=3&&day('Calendario'[Data])<=25,3,
if(MONTH('Calendario'[Data])=3&&day('Calendario'[Data])>=26||MONTH('Calendario'[Data])=4&&day('Calendario'[Data])<=22,4,
if(MONTH('Calendario'[Data])=4&&day('Calendario'[Data])>=23||MONTH('Calendario'[Data])=5&&day('Calendario'[Data])<=20,5,
if(MONTH('Calendario'[Data])=5&&day('Calendario'[Data])>=21||MONTH('Calendario'[Data])=6&&day('Calendario'[Data])<=17,6,
if(MONTH('Calendario'[Data])=6&&day('Calendario'[Data])>=18||MONTH('Calendario'[Data])=7&&day('Calendario'[Data])<=15,7,
if(MONTH('Calendario'[Data])=7&&day('Calendario'[Data])>=16||MONTH('Calendario'[Data])=8&&day('Calendario'[Data])<=12,8,
if(MONTH('Calendario'[Data])=8&&day('Calendario'[Data])>=13||MONTH('Calendario'[Data])=9&&day('Calendario'[Data])<=9,9,
if(MONTH('Calendario'[Data])=9&&day('Calendario'[Data])>=10||MONTH('Calendario'[Data])=10&&day('Calendario'[Data])<=7,10,
if(MONTH('Calendario'[Data])=10&&day('Calendario'[Data])>=8||MONTH('Calendario'[Data])=11&&day('Calendario'[Data])<=4,11,
if(MONTH('Calendario'[Data])=11&&day('Calendario'[Data])>=5||MONTH('Calendario'[Data])=12&&day('Calendario'[Data])<=2,12,
if(MONTH('Calendario'[Data])=12&&day('Calendario'[Data])>=3||MONTH('Calendario'[Data])=12&&day('Calendario'[Data])<=31,13)))))))))))))

regards

[Anonymous]

Thank you!