Week per month that starts over every 4 weeks

[victoryamaykin]

I want to count each sales week of the month from 1 to 4, so that I can apply a color to the week ( 1 = red, 2 = blue, etc.). 

Here's what I tried so far, which works up until the end of February. I want to restartt the count from 1 to 4 for the week numbers as shown in the table below. Thanks for your time. 

        "Week Ending", [Date] - WEEKDAY( [Date], 1) + 7, 
        // Use the last week number of month to reset the counting, min week number = 1, max week number = 4
        "Week in Month", IF( WEEKNUM( [Date]) > 4, WEEKNUM( [Date] ) - 4, WEEKNUM( [Date]))

Date	Week in Month	Desired Week in Month
2/18/2024	4	4
2/19/2024	4	4
2/20/2024	4	4
2/21/2024	4	4
2/22/2024	4	4
2/23/2024	4	4
2/24/2024	4	4
2/25/2024	5	1
2/26/2024	5	1
2/27/2024	5	1
2/28/2024	5	1
2/29/2024	5	1
3/1/2024	5	1
3/2/2024	5	1
3/3/2024	6	2
3/4/2024	6	2
3/5/2024	6	2
3/6/2024	6	2
3/7/2024	6	2
3/8/2024	6	2
3/9/2024	6	2

[v-kaiyue-msft · Accepted solution]

Hi @victoryamaykin ,

As @Jihwan_Kim  said, you can use the mod function to process the data:

Column =
VAR _num = MOD([Desired Week in Month],4)
RETURN
IF(_num = 0, 4, _num)

Let me explain in detail how to change the background color of this week.
1. After creating the table, right-click the date field -> Conditional Formatting -> Background Color.

2. Make the following settings in the window.

3. The final result is shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[victoryamaykin]

Thank you for the detailed response. It's working as intended. I used a custom column to make a legend for future visuals. Thanks again

[Jihwan_Kim]

Hi,

One of ways is to use MOD dax function.

Returns the remainder after a number is divided by a divisor