Calculating value for last two weeks

[Anonymous]

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?

[Anonymous]

Thank you SOOOOOOOOOOO much!

[smpa01 · Accepted 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

[Anonymous]

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

[Anonymous]

Thank you SOOOOOO much!