Measure that only uses 'previous week' values

[michaelsparrow]

Hi,

I'm got a bunch of data and I am trying to graph it so that everytime it is opened the graph only shows the previous weeks information. By previous week I mean the Monday-Sunday of the previous week, regardless of what day is it of this week; not the previous 7 days.

How would I do this?

Cheers

[Anonymous · Accepted solution]

Create a new column in either your Date Dimension table (if you are using one) or your Values table.  Have this column be called "isLastWeek".

Make this column a formula that check whether that line is in the week prior to the current week, this would then store TRUE if it is and FALSE if it isn't.

On your reports, make a Report or Page filter use this column and only show values that are TRUE.

So how do we know what week is last week?  Well we could use the WEEKNUM and YEAR functions to do this.  Unless this week is the first week of the year, last week will be WEEKNUM minus 1 with the same year.  So i'd do something like:

isLastWeek = IF(
    WEEKNUM(TODAY(), 1) = 1,
    IF(
        AND(
            YEAR('Dim - Date Table'[Date]) = YEAR(TODAY()) - 1,
            WEEKNUM('Dim - Date Table'[Date]) = WEEKNUM(DATE(YEAR(TODAY()), MONTH(12), DAY(31)))
        ),
        TRUE(),
        FALSE()
    ),
    IF(
        AND(
            YEAR('Dim - Date Table'[Date]) = YEAR(TODAY()),
            WEEKNUM('Dim - Date Table'[Date], 1) = WEEKNUM(TODAY(), 1)
        ),
        TRUE(),
        FALSE()
    )
)

Note my code uses a table called 'Dim - Date Table'

[smarguin]

Hi

I have tested the code and have adjusted for week commencing monday....

isLastWeek = IF(
WEEKNUM(TODAY(), 2) = 1,
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY())-1,
WEEKNUM('Ref Date'[Date]) = WEEKNUM(DATE(YEAR(TODAY()), MONTH(12), DAY(31)))
),
TRUE(),
FALSE()
),
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY()),
WEEKNUM('Ref Date'[Date], 2) = WEEKNUM(TODAY(), 2)-1
),
TRUE(),
FALSE()
)
)

[smarguin]

Same code but for this week

isThisWeek = IF(
WEEKNUM(TODAY(), 2) = 1,
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY())-1,
WEEKNUM('Ref Date'[Date]) = WEEKNUM(DATE(YEAR(TODAY()), MONTH(12), DAY(31)))
),
TRUE(),
FALSE()
),
IF(
AND(
YEAR('Ref Date'[Date]) = YEAR(TODAY()),
WEEKNUM('Ref Date'[Date], 2) = WEEKNUM(TODAY(), 2)
),
TRUE(),
FALSE()
)
)