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,

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.