Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure formula with SamePeriodLastYear not working

I have a RawTable with such values:

 

Users | TimeKey

46577 | 2017Q1

78776 | 2017Q2

89764 | 2017Q3

87876 | 2017Q4

49874 | 2018Q1

 

Now I want to create a measure column with the value from the same period last year. Meaning that in row 2018Q1 the measure value would be 46577. The TimeKey Column is linked to a calculated date dimension (and there with the Quartar Column).

I tried it with "Measure = CALCULATE(Sum(RawTable[Users]);SAMEPERIODLASTYEAR(DateDimension[Date]))". This results in empty value. 

Second try was "Measure = CALCULATE(Sum(RawTable[Users]);FILTER(RawTable;RawTable[TimeKey]=(LEFT(RawTable[TimeKey];4)-1 & RIGHT(RawTable[TimeKey];2)))).  (absolutely not a nice solution....)

 

Can somebody give me a hint?

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    For your case, just adjust the formula as below:

    Measure 2 = 
CALCULATE (
    SUM ( RawTable[Users] ),
    FILTER (
        ALLEXCEPT(  RawTable,RawTable[AdditionalDim] ),
        RawTable[TimeKey]
            = (
                LEFT ( MAX ( RawTable[TimeKey] ), 4 ) - 1
                    & RIGHT ( MAX ( RawTable[TimeKey] ), 2 )
            )
    )
)

    Use ALLEXCEPT instead of ALL

    Result:

    Best Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous 

    You could use this formula to add a measure

    Measure 2 = 
CALCULATE (
    SUM ( RawTable[Users] ),
    FILTER (
        ALL ( RawTable ),
        RawTable[TimeKey]
            = (
                LEFT ( MAX ( RawTable[TimeKey] ), 4 ) - 1
                    & RIGHT ( MAX ( RawTable[TimeKey] ), 2 )
            )
    )
)

    Result:

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so far. 

      For this case where only the TimeKey is choosen it works. As soon as I choose an additional dimension it does not work anymore. How can I "respect" the choosen dimensions, and overwrite only the "TimeKey"?

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Anonymous 

        For your case, just adjust the formula as below:

        Measure 2 = 
CALCULATE (
    SUM ( RawTable[Users] ),
    FILTER (
        ALLEXCEPT(  RawTable,RawTable[AdditionalDim] ),
        RawTable[TimeKey]
            = (
                LEFT ( MAX ( RawTable[TimeKey] ), 4 ) - 1
                    & RIGHT ( MAX ( RawTable[TimeKey] ), 2 )
            )
    )
)

        Use ALLEXCEPT instead of ALL

        Result:

        Best Regards,

        Lin