Create custom date period (new column/slicer) from and to date ranges

[baadshah]

Hi, 

I have created a new table with a list of dates starting from the 15/12/2019 going to 14/12/2022 using the below function:

Calender = CALENDAR(DATE(2018,12,15),DATE(2025,12,14))

I would like to create a new column on the same table with my own custom period for example:

15th Dec to 14th of Jan = "Period 1"

15th Jan to  14th Feb = "Period 2"
15th Feb to 14th Mar = "Period 3"

and so forth. 

My calender starts from the 15th Dec. 

The year element of the date will will be a seperate filter so the above period can be filtered by year. 

[Anonymous · Accepted solution]

Hi @baadshah ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a alculated column.

CustomPeriod = 
VAR CurrentDate = 'Calender'[Date]
VAR _Year = YEAR(CurrentDate)
VAR _Month = MONTH(CurrentDate)
VAR _Day = DAY(CurrentDate)

RETURN

SWITCH(
        TRUE(),
        OR(_Day >= 15 && _Month = 12 , _Day < 15 && _Month = 1), "Period 1",
        _Day >= 15 && MONTH('Calender'[Date]) = _Month , "Period"&" " &_Month+1,
        _Day < 15 && MONTH('Calender'[Date]) = _Month,"Period"& " " &_Month
    )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Sahir_Maharaj]

Hello @baadshah,

Can you please try:

CustomPeriod =
VAR StartMonth = MONTH([Date])
VAR StartDay = DAY([Date])
VAR EndMonth = IF(StartMonth = 12, 1, StartMonth + 1)
VAR EndDay = IF(StartMonth = 12, 14, StartDay - 1)
VAR PeriodNumber = IF(StartMonth = 12, 1, StartMonth)

RETURN
    "Period " & PeriodNumber

[baadshah]

Hi @Sahir_Maharaj. Thank you for getting back to me. 

This function may need a tweak because it's displaying "Period 1" from 15/12/2018 to 31/1/2019. I need it to display:

15/12/2018 to 14/1/2019 as "Period 1" 
15/2/2019 to 14/3/2019 as "Period 2"
and so forth