Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating previous values

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 ca...
General Comment
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
VahidDM
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    Does not seem to work 

     

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.DivisionLocationFiscal Week BeginsFiscal Week EndsFiscal Week NumHire_CountTerm_CountNet Hires
        WEST1012/25/2021 0:0012/31/2021 0:004802-2
        WEST111/1/2022 0:001/7/2022 0:004903-3
        WEST121/8/2022 0:001/14/2022 0:0050101
        WEST101/15/2022 0:001/21/2022 0:0051413
        WEST101/22/2022 0:001/28/2022 0:0052211
        WEST101/29/2022 0:002/4/2022 0:00101-1
        WEST102/5/2022 0:002/11/2022 0:002101
        WEST105012/25/2021 0:0012/31/2021 0:004812-1
        WEST501/1/2022 0:001/7/2022 0:004902-2