Forum Discussion

michaelsparrow's avatar
9 years ago
Solved

Measure that only uses 'previous week' values

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 previo...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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'