topic Week per month that starts over every 4 weeks in DAX Commands and Tips

Week per month that starts over every 4 weeks

victoryamaykin — Mon, 26 Feb 2024 19:32:01 GMT

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

Re: Week per month that starts over every 4 weeks

Jihwan_Kim — Mon, 26 Feb 2024 20:24:59 GMT

Hi,

One of ways is to use MOD dax function.

Returns the remainder after a number is divided by a divisor

Re: Week per month that starts over every 4 weeks

Anonymous — Tue, 27 Feb 2024 02:15:53 GMT

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.

Re: Week per month that starts over every 4 weeks

victoryamaykin — Tue, 27 Feb 2024 15:26:21 GMT

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