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.

BiNavPete's avatar
BiNavPete
Resolver III
3 years ago

Hi Mischi 

See attached PBIX.

It is best to add the period column in Power Query rather than DAX.
In the example I establish that P1 starts on 1/1/2023, so brought in dates for whole year 2023 as an example.

1 - Create an Index Column starting with 1. This numbers every row from 1 to 365

2 - Add Column Step1_PeriodNo - this performs integer division on the index to give a division without any remainder. But this doesn't handle the 28th/56th etc day. So if statement in PQ to handle.
3 - Add Column Step2_PeriodNo - concatenate P & the number from the previous step.

Steps 2 and 3 above are not needed, but there just for learning. The column Period combines this logic and is all you need in your model.

In order to get the sorting correct for Power BI visuals I put a lead zero in by using Text.PadStart. So if you're happy with P06 rather than P6 we're all done. If you absolutely need it to read P6 then once in Power BI put a sort on Step1_PeriodNo.

There are a couple of other things to manage here, eg multiple years anf the last day of the year - P13 or P1.
But this should get you going.

Another resource to look at is EDNA expert Melissa de Kort's extended date table which manages ISO weeks etc and may just have what you need.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181