Weeks (Calendar) vs Just Weeks in Date Slicer

[Farank]

Hello,

I was wondering if someone could help me understand the difference between Weeks and Weeks (Calendar) on a date slicer.

I have a matrix sorted by the last 5 calendar weeks and it cuts of a portion of a measure.  Please see the Total Stages row below (just a distinct count of a specific column):

If I expand the slicer to 6 Weeks (Calendar), it no longer cuts off data for week 39:

If I use just Weeks, it doesn't appear to cut off the data, but it does show a different value:

What is the difference between the two? It appears that just Weeks is going from a Tuesday to today's date (also a Tuesday) and Weeks (Calendar) is from Sunday to Saturday. How can I show the last 5 Weeks (Calendar) without it cutting off the data for the last week?

Any help would be greatly appreciated. 

Thanks.

[Farank]

Why do we have to create measures and columns to have Power BI not cut off the data for the last week in the matrix as shown in the pictures in the first post?  Why does Power BI do this?

[v-stephen-msft]

Hi @Farank ,

Sorry to reply so late.

If you select Weeks (Calendar), the complete calendar week is always considered for the period. This means it will start on Saturday and end on Sunday, because November 1st to November 2nd is not a complete calendar week (November is the current day in the picture).

If you select Weeks, it just goes back seven days. If the relative date is the last 2 weeks, it just goes back 14 days from the current day.

Best Regards,

Stephen Tao

[Farank]

Hi @v-stephen-msft
For week 39 in the case of Weeks (Calendar), it cuts off data for that week unless you extend the relative date past that week. Should it not show the full calendar week of data there without having to extend past it? That's what I don't understand. I believe your response states that week 39 would be from Saturday to Sunday but it's cut off unless I extend to week 38. Then it does not cut off data for week 39. It doesn't appear to be accounting for all days in that week as you stated in your response.

[Farank]

This works. Thanks @v-stephen-msft . May I ask what the program is doing to cut off the last week in the first place? Is this something that will be fixed in the future or is it necessary that Power BI works this way?

[v-stephen-msft]

Hi @Farank ,

What does the program is doing to cut off the last week mean?

Best Regards,

Stephen Tao

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

[v-stephen-msft]

Hi @Farank ,

Here's one workaround.

Could well be other ways of handling this.

Dummy Power BI model here.

1.Create a seperate table.

Table 2 = DISTINCT(SELECTCOLUMNS('Table',"Last n weeks(calendar)",[Week]))

 

2.Create a measure.

Measure = 
VAR _wt =
    WEEKNUM ( TODAY (), 2 )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Last n weeks(calendar)] ),
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER (
                'Table',
                [Week] <= _wt
                    && [Week]
                        >= _wt - SELECTEDVALUE ( 'Table 2'[Last n weeks(calendar)] )
            )
        )
    )

3.The result is as follows. It show the last 5 Weeks (Calendar) without it cutting off the data for the last week.

Best Regards,

Stephen Tao

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

[Farank]

Hi @v-stephen-msft ,
I attempted to recreate your solution for my application but I'm using measures within the matrix, not columns, and I'm not fully following.

Here is sample:
Test pbix

[v-stephen-msft · Accepted solution]

Hello @Farank ,

Check if this file is what you want.

Best regards

Stephen Tao

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Farank , Calendar week should call week start date Sunday. The other one should rolling week*7 days

[Farank]

@amitchandak 

Yes but why does it cut off data? If I extend to 6 weeks the value for week 39 changes to what it should be. How do I work around or mitigate this problem?