Forum Discussion

Mischi's avatar
Mischi
Frequent Visitor
3 years ago
Solved

Formatting Date

Hello. Our company works in Periods (a period of 4 weeks, not nessecarily lining up with a month) and I need to format a date value accordingly. If a date is on or in between 21. May 2023 and 17. J...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    3 years ago

    Hey, 

    You can perform this task by creating a new column and by after that using DAX formula 

    PeriodCode = 

    VAR CurrentDate = YourTableName[DateColumn]

    VAR StartDate = DATE(2023, 5, 21)  // Update with your desired start date

    VAR DaysDiff = DATEDIFF(StartDate, CurrentDate, DAY)

    VAR PeriodNumber = CEILING(DaysDiff / 28, 1)

     

    RETURN

        SWITCH(

            TRUE(),

            AND(DaysDiff >= 0, DaysDiff < 28), "P" & PeriodNumber,

            AND(DaysDiff >= 28, DaysDiff < 56), "P" & (PeriodNumber + 1),

            AND(DaysDiff >= 56, DaysDiff < 84), "P" & (PeriodNumber + 2),

            // Add more cases for other periods as needed

            "Unknown"

        )

    After that in modeling tab change the format into text and after using the new calculated column you can achieve your desired output.

    Thank you. Hope this will help.

ryan_mayu's avatar
ryan_mayu
Super User
3 years ago

Mischi 

i have some questions?

1. the period is always start from P6? What about the date before 2023/5/21?

2. what about the next year? will we have a new start date or just keep the roation from 2023/5/21?

  • Mischi's avatar
    Mischi
    Frequent Visitor
    3 years ago

    First period always starts around New Years, plus/minus a few days. Each period runs for 4 weeks and starts on the first sunday and ends on the last Saturday. There are 13 periods and after the 13th period a new rotation starts.

    I found a solution now though.