Forum Discussion
Calculating value for last two weeks
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...
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
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?
Last 2 Weeks =
VAR week =
MAXA( 'Terms & Hires SQL'[Fiscal Week Ends])
VAR cal =
CALCULATE (
SUM ( 'Terms & Hires SQL'[Net Hires]),
FILTER (
ALL ('Terms & Hires SQL'),
'Terms & Hires SQL'[Fiscal Week Ends]
>= CALCULATE ( MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] ), 'Terms & Hires SQL'[Fiscal Week Ends] < week, ALL ('Terms & Hires SQL') )
&& 'Terms & Hires SQL'[Fiscal Week Ends] <= week
)
)
RETURN
cal