Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
It works Thank you so much...
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |