Calculating previous values

[Anonymous]

A super user - smpa01 has helped me previously with this but I need additional help.. 

I am trying to calculate values for "Net Hires" the last two weeks, as you can see in my screen shot (the calculation is showing wrong because, I assume, it does not consider the newly added colums - Region and location) 

How do I ensure that when the page is being filtered, values from this measure change accordingly? I have few additional columns (Location and Region) in 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

Data Sample: 

Location Table SQL.Division	Location	Fiscal Week Begins	Fiscal Week Ends	Fiscal Week Num	Hire_Count	Term_Count	Net Hires
WEST	10	12/25/2021 0:00	12/31/2021 0:00	48	0	2	-2
WEST	11	1/1/2022 0:00	1/7/2022 0:00	49	0	3	-3
WEST	12	1/8/2022 0:00	1/14/2022 0:00	50	1	0	1
WEST	10	1/15/2022 0:00	1/21/2022 0:00	51	4	1	3
WEST	10	1/22/2022 0:00	1/28/2022 0:00	52	2	1	1
WEST	10	1/29/2022 0:00	2/4/2022 0:00	1	0	1	-1
WEST	10	2/5/2022 0:00	2/11/2022 0:00	2	1	0	1
WEST	1050	12/25/2021 0:00	12/31/2021 0:00	48	1	2	-1
WEST	50	1/1/2022 0:00	1/7/2022 0:00	49	0	2	-2

[VahidDM]

HI @Anonymous 

You used ALL in the formula that blocked all filters, try this:

Last 2 Weeks =
VAR _W1 =
    MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] )
VAR _W2 =
    CALCULATE (
        MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] ),
        FILTER (
            ALL ( 'Terms & Hires SQL' ),
            'Terms & Hires SQL'[Fiscal Week Ends] < week
        )
    )
VAR cal =
    CALCULATE (
        SUM ( 'Terms & Hires SQL'[Net Hires] ),
        FILTER (
            ALL ( 'Terms & Hires SQL'[Net Hires] ),
            'Terms & Hires SQL'[Fiscal Week Ends] >= _W2
                && 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1
        )
    )
RETURN
    cal

https://docs.microsoft.com/en-us/dax/all-function-dax

Check this :  https://www.vahiddm.com/post/weekly-time-intelligence-dax

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

@VahidDM does not seem to work, trying to tinker with it now...

Last 2 Weeks =

VAR _W1 =

    MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] )

VAR _W2 =

    CALCULATE (

        MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] ),

        FILTER (

            ALL ( 'Terms & Hires SQL' ),

            'Terms & Hires SQL'[Fiscal Week Ends] < week (This only lets me enter in _W1 as the variable)

        )

    )

VAR cal =

    CALCULATE (

        SUM ( 'Terms & Hires SQL'[Net Hires] ),

        FILTER (

            ALL ( 'Terms & Hires SQL'[Net Hires] ),

            'Terms & Hires SQL'[Fiscal Week Ends] >= _W2

                && 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1

        )

    )

RETURN

    cal

[VahidDM]

@Anonymous 

My bad! try this:

Last 2 Weeks =
VAR _W1 =
    MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] )
VAR _W2 =
    CALCULATE (
        MAXA ( 'Terms & Hires SQL'[Fiscal Week Ends] ),
        FILTER (
            ALL ( 'Terms & Hires SQL' ),
            'Terms & Hires SQL'[Fiscal Week Ends] < _W1
        )
    )
VAR cal =
    CALCULATE (
        SUM ( 'Terms & Hires SQL'[Net Hires] ),
        FILTER (
            ALL ( 'Terms & Hires SQL'[Net Hires] ),
            'Terms & Hires SQL'[Fiscal Week Ends] >= _W2
                && 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1
        )
    )
RETURN
    cal

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Does not seem to work 

[VahidDM]

@Anonymous 

Can you share your PBIX file with me?

Share that on https://wetransfer.com/ and past the link here

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

I cannot, I am sorry, it's all coming from SQL and  there is a lot of sensitve data but here is a sample of the data 

Location Table SQL.Division	Location	Fiscal Week Begins	Fiscal Week Ends	Fiscal Week Num	Hire_Count	Term_Count	Net Hires
WEST	10	12/25/2021 0:00	12/31/2021 0:00	48	0	2	-2
WEST	11	1/1/2022 0:00	1/7/2022 0:00	49	0	3	-3
WEST	12	1/8/2022 0:00	1/14/2022 0:00	50	1	0	1
WEST	10	1/15/2022 0:00	1/21/2022 0:00	51	4	1	3
WEST	10	1/22/2022 0:00	1/28/2022 0:00	52	2	1	1
WEST	10	1/29/2022 0:00	2/4/2022 0:00	1	0	1	-1
WEST	10	2/5/2022 0:00	2/11/2022 0:00	2	1	0	1
WEST	1050	12/25/2021 0:00	12/31/2021 0:00	48	1	2	-1
WEST	50	1/1/2022 0:00	1/7/2022 0:00	49	0	2	-2

[VahidDM]

Hi @Anonymous 

Try this:

Last 2 Weeks = 
VAR _W1 =
    MAX ( 'Terms & Hires SQL'[Fiscal Week Ends] )
VAR _W2 =
    CALCULATE (
        MAX ( 'Terms & Hires SQL'[Fiscal Week Ends] ),
        FILTER (
            ALL ( 'Terms & Hires SQL' ),
            'Terms & Hires SQL'[Fiscal Week Ends] < _W1
        )
    )
VAR cal =
    CALCULATE (
        SUM ( 'Terms & Hires SQL'[Net Hires] ),
        FILTER (
            ALL ( 'Terms & Hires SQL'),
            'Terms & Hires SQL'[Fiscal Week Ends] >= _W2
                && 'Terms & Hires SQL'[Fiscal Week Ends] <= _W1
        )
    )
RETURN
    _W2

In your sample Data the result is zero (11-Feb net Hire is 1 and 4-Feb it's -1)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

It's a date and if created as a new column and formatted as a number the values are the same