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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Does not seem to work