Forum Discussion

Pawan's avatar
Pawan
Frequent Visitor
3 years ago
Solved

Previous Year DAX Calculation

Hi Team

 

Please can you help me on below issue. I have created a table like below in power bi.

By default current month selection is applied and calculated CY and PY measures like below

 

CY= CALCULATE( Sum(SalesNetAmountGBP]),Date'[CurrentDayOffset]<=0)

PY = CALCULATE([CY], SAMEPERIODLASTYEAR('Date'[Date]))
 
Issue: When i have selected 2022 full year PY measure is showing data upto 2021 december 31st instead of 2021 december 5th (today). Please can you help to write DAX for  PY measure to show last year today date data dynamically .

 

 

Thanks in advance

 

v-janeyg-msft 

DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Pawan 

    please try

    PY =
    CALCULATE (
    [CY],
    FILTER (
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    FORMAT ( [Date], "MMDD" ) <= FORMAT ( TODAY (), "MMDD" )
    )
    )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Pawan 

    please try

    PY =
    CALCULATE (
    [CY],
    FILTER (
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    FORMAT ( [Date], "MMDD" ) <= FORMAT ( TODAY (), "MMDD" )
    )
    )

    • Pawan's avatar
      Pawan
      Frequent Visitor
      3 years ago

      Hi tamerj1 

       

      Tha above solution is working for both month and year selection.

       

      Thanks

      Pawan

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Pawan ,

     

    PY = CALCULATE([CY], SAMEPERIODLASTYEAR('Date'[Date]), format([Date],"MMDD") <= format(today() ,"MMDD")  )

    • Pawan's avatar
      Pawan
      Frequent Visitor
      3 years ago

      amitchandak thank you for your reply. The above solution works only for complete year(2022) selection. But is breaking when we select month or quarter. The measure should be dynamic based on Date selection.