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

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

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

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