how to display current week and prior week dynamically?

[AnandNamburi]

Hi All,

I have a requirement to display current week sales and prior week sales in two pages(1 page for current week sles and other for prior week sales) and the values should change dynamically with our date.

Example:

Current week as of today is 6/Dec to 12/Dec and if week changes it need to change automatically 13/Dec to 19/Dec.

Anyone help me how we can acheive this.

Thanks,

Anand

[AnandNamburi]

It works Thank you so much...

[Anonymous · Accepted solution]

Hi @AnandNamburi,

There are a few different ways to achieve this. Perhaps the simplest one is to build your report page, duplicate it, and set page-level filters on each of the pages using the date column and "relative date filtering" as shown below:

Alternatively you can create calculated columns to determine whether a date is within the current or prior calendar week and use that as a filter. You can do this by for example creating a single column like below, which will return whether the value is within the current or prior week, and will leave the cell blank if it falls outside both those categories:

week_category =
IF(
    WEEKNUM( 'example_table'[Date].[Date] ) = WEEKNUM( TODAY() ),
    "Current week",
    IF(
        WEEKNUM( 'example_table'[Date].[Date] ) = WEEKNUM( TODAY() ) - 1,
        "Prior week"
    )
)

Another option is to create two boolean columns, one returning TRUE if the date is in the current week, and another returning TRUE if the date is in the prior week:

is_current_week =
IF(
    WEEKNUM( 'example_table'[Date].[Date] ) = WEEKNUM( TODAY() ),
    TRUE(),
    FALSE()
)

is_prior_week =
IF(
    WEEKNUM( 'example_table'[Date].[Date] ) = WEEKNUM( TODAY() ) - 1,
    TRUE(),
    FALSE()
)

I hope this helps - have a great weekend!