Forum Discussion

daniel_krantz_4's avatar
daniel_krantz_4
Helper I
2 years ago
Solved

Transitioning from Tableau - calculated Measure

I'm having alot of trouble with this one. In Tableau, I have a measure called "% Difference" - calculated as "ZN([%Pos Response]) - LOOKUP(ZN([%Pos Response]), -1)". This allows me, when I put Years ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi daniel_krantz_4 

     

    Maybe you can try this:

    Here I create a measure:

    % Difference =
VAR _currentYear =
    YEAR ( MAX ( 'Table'[Date] ) )
VAR _previousYear =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'TABLE' ), YEAR ( 'TABLE'[Date] ) < _currentYear )
    )
VAR CurrentYearValue =
    CALCULATE (
        [%Pos Response],
        FILTER ( ALLSELECTED ( 'Date' ), YEAR ( 'Table'[Date] ) = _currentYear )
    )
VAR PreviousYearValue =
    CALCULATE (
        [%Pos Response],
        FILTER ( ALLSELECTED ( 'Date' ), YEAR ( 'Table'[Date] ) = _previousYear )
    )
RETURN
    IF (
        NOT ISBLANK ( CurrentYearValue ),
        ( CurrentYearValue - PreviousYearValue ) / PreviousYearValue,
        BLANK ()
    )

     You can change the 'Table'[Date] into your own table's Date column.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi daniel_krantz_4 

 

Maybe you can try this:

Here I create a measure:

% Difference =
VAR _currentYear =
    YEAR ( MAX ( 'Table'[Date] ) )
VAR _previousYear =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'TABLE' ), YEAR ( 'TABLE'[Date] ) < _currentYear )
    )
VAR CurrentYearValue =
    CALCULATE (
        [%Pos Response],
        FILTER ( ALLSELECTED ( 'Date' ), YEAR ( 'Table'[Date] ) = _currentYear )
    )
VAR PreviousYearValue =
    CALCULATE (
        [%Pos Response],
        FILTER ( ALLSELECTED ( 'Date' ), YEAR ( 'Table'[Date] ) = _previousYear )
    )
RETURN
    IF (
        NOT ISBLANK ( CurrentYearValue ),
        ( CurrentYearValue - PreviousYearValue ) / PreviousYearValue,
        BLANK ()
    )

 You can change the 'Table'[Date] into your own table's Date column.

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

daniel_krantz_4's avatar
daniel_krantz_4
Helper I
2 years ago

Thank you! I made some small changes to fit my data and realized I didn't need the divide statement, but this works! Here was the final code:

 

%Difference =
VAR _currentYear = MAX('Data'[Year])
VAR _previousYear =CALCULATE(MAX('Data'[Year]),
FILTER(ALLEXCEPT('Data', 'Data'[QGroup], 'Data'[Question]),'Data'[Year] < _currentYear))

VAR CurrentYearValue =CALCULATE([%Pos Response],
FILTER(ALLEXCEPT('Data', 'Data'[QGroup], 'Data'[Question]),'Data'[Year] = _currentYear))
VAR PreviousYearValue =CALCULATE([%Pos Response],
FILTER(ALLEXCEPT('Data', 'Data'[QGroup], 'Data'[Question]),'Data'[Year] = _previousYear))
RETURN
IF(NOT ISBLANK(CurrentYearValue),CurrentYearValue - PreviousYearValue, BLANK())