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 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.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
General Comment
Need Help
Tutorial Requests

8 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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'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/