How to calculate week number based on changing start dates?

[SRJPBI]

Hi all,

I have a table within my Power BI report which looks a bit like this:

A new row is added daily, with the date column defined as the current day. That said, I would like the solution to work even if the data load for some days fails, just in case.

There are never two offers active at the same time.

Offers can start in one year and finish in another.

Offers can begin and end on any day of the week.

Offers are not necessarily active for the same length of time.

I want to add a column flagging what week an offer is in on each date. However, the weeks should be defined as Sunday to Saturday. So the desired result would look like this:

On 31/12/23 offer_week becomes 2 because that day is a Sunday, even though the offer is only on its second day. On 05/01/24 offer_week becomes 1 again because it is the first day of a new offer.

Does anyone know how to do this please?

[Anonymous · Accepted solution]

Hi @SRJPBI 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a column. First check the date to see what day of the week it is. Determine if it's the first day of a new offer or if it's a Saturday.

offer_week = 
var min_date = 
    CALCULATE(
        MIN('Table'[date]), 
        FILTER(
            'Table', 
            'Table'[offer] = EARLIER('Table'[offer])
        )
    )
RETURN 
    IF(
        'Table'[date] = min_date 
        || 
        WEEKDAY('Table'[date], 2) = 6, 
        1, 
        2
    )

Here is the result.

Regards,

Nono Chen

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