Slicer - Monthly Week range selection

[rushikale0106]

Hello,

I am looking for the DAX which will calculate the week range as per the following conditions:

Each column should be labelled with the relevant week. Week labelling should have the following format W# Mon e.g. W1 Oct. The week numbering is determined as follows:

A) A week can have a number 1, 2, 3, 4, or 5.
B) W1 of the month is the first week in the month that has 3 or more days in that month Monday-Friday.
- For example, the week of Monday, August 30, 2021 - Sunday, September 5, 2021is "W1 Sep" because Monday and Tuesday of the week were in August, but Wednesday, Thursday, and Friday of that week were in September. So, since of Monday-Friday, three days were in September, it is considered W1 Sep, NOT W5 Aug
C) After W1 of a month, the following weeks are numbered 2, 3, or 4 (or sometimes 5) in order until the next W1 is encountered.

Please let me know if need more details.

Thanks in Advance!

[v-yingjl]

Hi @rushikale0106 ,

Condition A) and C) seems not very clear, how do you define the calculation logic, especially 'until the next W1 is encountered.'

It is clear to calculate the conditional week count based on Condition B, like:

MonthWeekDayCount = 
CALCULATE(
    COUNT('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Total weeknum] = EARLIER('Table'[Total weeknum]) &&
        'Table'[Date].[MonthNo] = EARLIER('Table'[Date].[MonthNo]) &&
        WEEKDAY('Table'[Date],2) <> 6 && WEEKDAY('Table'[Date],2) <> 7
    )
)

You can refer the current test file to consider sharing more details about it.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.