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 | 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 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
4 Replies
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 calHi 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))RETURNcal