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.
Hello,
I have a dataset with fiscal weeks, here is a sample:
Fiscal Year | Fiscal Week | Net Hires | Net Hires Previous Two Weeks | Date |
2021 | 48 | 100 |
| 12/27/2021 |
2021 | 49 | 50 | 150 | 01/03/2022 |
2021 | 50 | 150 | 200 | 01/10/2022 |
2021 | 51 | 30 | 180 | 01/17/2022 |
How do I calculate the total of net hires for the last two fiscal weeks from today? The last column shows what I am after.
I assume I first need to figure out what today is, what fiscal week it is in and then what are the two fiscal weeks from today?
Solved! Go to Solution.
@Anonymous you can use a measure like this
Measure =
VAR week =
MAX ( tbl[Fiscal Week] )
VAR cal =
CALCULATE (
SUM ( tbl[Net Hires] ),
FILTER (
ALL ( tbl ),
tbl[Fiscal Week]
>= CALCULATE ( MAX ( tbl[Fiscal Week] ), tbl[Fiscal Week] < week, ALL ( tbl ) )
&& tbl[Fiscal Week] <= week
)
)
RETURN
cal
Thank you SOOOOOOOOOOO much!
@Anonymous you can use a measure like this
Measure =
VAR week =
MAX ( tbl[Fiscal Week] )
VAR cal =
CALCULATE (
SUM ( tbl[Net Hires] ),
FILTER (
ALL ( tbl ),
tbl[Fiscal Week]
>= CALCULATE ( MAX ( tbl[Fiscal Week] ), tbl[Fiscal Week] < week, ALL ( tbl ) )
&& tbl[Fiscal Week] <= week
)
)
RETURN
cal
Hi @smpa01,
I adjusted the statement a little bit to fit my everchanging dataset, how do I ensure that when the page is being filtered, values from this measure change accordingly? I have few additional columns the dataset can be filtered by but I cannot figure out where I should add them to the statement. Are you able to help me?
Thank you SOOOOOO much!
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.