Formatting Date

[Mischi]

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. June 2023 it must Display "P6" (for period 6).

Continuing in a 4 week roation:

18. June 2023 - 15. July 2023 "P7"

16. July 2023 - 12. August 2023 "P8"

and so on.

Im pretty new to all this and an IF function didnt work and gave me an error message.

Can anyone give me a hand with this?

Thanks

[grazitti_sapna · Accepted solution]

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]

@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]

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.

[BiNavPete]

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