Previous Year calculation

[BishwaR]

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 Current Year CY  using MAX ( Sales Date) but when I try to calculate Previous year the dax

Max(Sale Date), -1, YEAR does not return the correct result.  How to get the previous year. I cannot use the time intelligence functions because in the report there is no date column.  In the worse case I will use the time column in report and hide it 

Thanks

Bishwa

[CNENFRNL]

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 · Accepted solution]

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

[BishwaR]

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]

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.