Forum Discussion

gvg's avatar
gvg
Post Prodigy
5 years ago
Solved

Measure does not work when applied as calculated column formula

Hi,

I have a simple table :

I need to add a column that contains sales of the previous year. I can easily calculate it with a measure like this:

 

Amount previous year = 
CALCULATE( sum ( tSales[Sales] ), 
                FILTER( ALL(tSales), tSales[Year] = MAX( tSales[Year])  - 1 ) 
)

 

 

The result is this:
However when I try to put this formula into a calculated column, it produces blank column. Why is it not working in calculated column?
Show and Tell
  • parry2k's avatar
    parry2k
    5 years ago

    gvg try this for calculated column

     

    Prev Year = 
VAR __prevYear = Sales[Year] - 1 
RETURN
CALCULATE ( SUM ( Sales[Amount] ), ALL ( Sales ), Sales[Year] = __prevYear )

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    gvg try this for calculated column

     

    Prev Year = 
VAR __prevYear = Sales[Year] - 1 
RETURN
CALCULATE ( SUM ( Sales[Amount] ), ALL ( Sales ), Sales[Year] = __prevYear )

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • gvg's avatar
    gvg
    Post Prodigy
    5 years ago

    Yep, that works. Than you. Why it works with a variable and not working with my measure?