Compare Year with Numer or Date

[CMOLINA]

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

[lukiz84 · Accepted solution]

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
      )
   

[Ashish_Mathur]

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.