Forum Discussion

BishwaR's avatar
BishwaR
Helper V
5 years ago
Solved

Previous Year calculation

I have a table like this and year column is availabe with 2020,2019 and 2018.  Dept   CY      PY    A      100    50    B       40     90    C       30    88   I am able to calculate  the Curre...
Need Help
  • BishwaR's avatar
    BishwaR
    5 years ago

    CNENFRNL  Thank you so much. It was a great help,

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, BishwaR , did you give a shot to DATEADD(Sales[Date], -1, YEAR)? It's a common way to filter down to previous year; but pls keep in mind that since it's a time intelligence function, results based on an incontiguous date column may vary in contrast to those based on a standard time table.

BishwaR's avatar
BishwaR
Helper V
5 years ago

Thank you CNENFRNL . I think I was not able to make my point clear to you so let me explain my situation.

When I use the dax Max(sales date) and put it in a card I get the following visual.

My question is what dax should I write to get the Previous year which is 2019 similarly in the card.

If I try to do Min( Sales date) I get 2018 but I need 2019. I tried using dateadd but it does not return the Year in the way I need. Thanks 

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi BishwaR ,

    Since there is no date column in your dataset, you use the year as a slicer and create measures like this:

    CY = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] )
            && 'Table'[Dept] IN DISTINCT ( 'Table'[Dept] )
    )
)

PY = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year]
            = SELECTEDVALUE ( 'Table'[Year] ) - 1
            && 'Table'[Dept] IN DISTINCT ( 'Table'[Dept] )
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.