Forum Discussion

CMOLINA's avatar
CMOLINA
Frequent Visitor
4 years ago
Solved

Compare Year with Numer or Date

hello, I need your help:

I have this Calculated Field: 

  • CALCULATE(Sales[Sales USD],FILTER('CALENDAR', 'CALENDAR'[Year]=2022))
 this is OK

 

but when I change for that:
  • Sales CY =
  • var CY=  year(LASTDATE('CALENDAR'[Date]))
  • Return
  • CALCULATE(Sales[Sales USD],FILTER('CALENDAR', 'CALENDAR'[Year]=CY))

 I need only the sales for the current year

 

Why PBi doing this? What is the correct solution? 😞

Thanks Team

Need Help
Show and Tell
  • lukiz84's avatar
    lukiz84
    4 years ago

    LASTDATE is calculated per row so for example in the row with 2021-10 the last year is 2021. To make it work the way you want it you have to write it like this:

     

    Sales CY = 
   var CY = CALCULATE(
      YEAR(LASTDATE('Calendar'[Date])),
      ALL(Calendar)
   )
   RETURN
      CALCULATE(
         SUM(Sales[Sales USD]),
         Calendar[Year] = CY
      )

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Assuming you already have a Date column in your Data Table, create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  Create a slicer of Year (dragged from the Calendar Table) and select 2022.  Write this measure

    Total sales = sum(Sales[Sales USD])

    Hope this helps.

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    4 years ago

    LASTDATE is calculated per row so for example in the row with 2021-10 the last year is 2021. To make it work the way you want it you have to write it like this:

     

    Sales CY = 
   var CY = CALCULATE(
      YEAR(LASTDATE('Calendar'[Date])),
      ALL(Calendar)
   )
   RETURN
      CALCULATE(
         SUM(Sales[Sales USD]),
         Calendar[Year] = CY
      )