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 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

Need Help

4 Replies

  • 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.