Forum Discussion

Yin's avatar
Yin
Frequent Visitor
8 years ago
Solved

How to calculate sameperiodlastyear ?

Dear all,

 

I have a table that I want to calculate sameperiodlastyear value, but since there are multi sameperiods in time column, the dax returns blank.  My formula is like: Value_sameperiodlastyear = CALCULATE(sum[value],SAMEPERIODLASTYEAR([monthyear])). 

 

My table example is as following:

monthyearcustomerValueValue_sameperiodlastyear
1/1/2017A1111111
1/1/2017B2222222
1/1/2017C3333333
1/1/2016A111 
1/1/2016B222 
1/1/2016C333 

Column "Value_sameperiodlastyear" is the result I want, by using the first three columns. 

 

Do you guys know how to write the correct formula? Many thanks!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Yin

     

    If you use the same formula as a MEASURE it will work

    Probably you are using it as Calculated Column

     

    = CALCULATE(sum(TableName[Value]),SAMEPERIODLASTYEAR(TableName[monthyear]))

     

    If you have to use a Calculated Column use this

     

    Column =
CALCULATE (
    SUM ( TableName[Value] ),
    ALLEXCEPT ( TableName, TableName[customer] ),
    SAMEPERIODLASTYEAR ( TableName[monthyear] )
)

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Yin

     

    If you use the same formula as a MEASURE it will work

    Probably you are using it as Calculated Column

     

    = CALCULATE(sum(TableName[Value]),SAMEPERIODLASTYEAR(TableName[monthyear]))

     

    If you have to use a Calculated Column use this

     

    Column =
CALCULATE (
    SUM ( TableName[Value] ),
    ALLEXCEPT ( TableName, TableName[customer] ),
    SAMEPERIODLASTYEAR ( TableName[monthyear] )
)