cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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. 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

1 ACCEPTED SOLUTION
Resolver II

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.

4 REPLIES 4
Resolver II

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.

Super User

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?

Proud to be a Super User!

Frequent Visitor

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.

Resolver III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors