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 = ...
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
      )
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.