Year formula - dynamically changing

[Anonymous]

Hey all,

I'm kindly ask for a little help regarding a calculated column or a metric. I need to create a field which to show me (to filter) data only for the last available completed year. For example if I have a bar chart and today is April 13th, 2020, I need to have the data filtered for the full 2019. If Today is January 1st, 2021, I need the data to be filtered automatically for the full 2020, and every year this field to be updated dynamically.

Is it possible?

Thank you.

[Greg_Deckler · Accepted solution]

Perhaps:

Measure = 
  VAR __Year = YEAR(TODAY())
RETURN
  SUMX(FILTER('Table',YEAR([Date]) = __Year - 1),[Column])

[amitchandak · Accepted solution]

In case you calendar end in the same year or date is selected

Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Measure =

var _max = year(Today())-1

Return

CALCULATE(SUM(Sales[Sales Amount]), Year(Sales[Sales date]) =_max)

or

Measure =

var _max = year(Today())-1

Return

CALCULATE(SUM(Sales[Sales Amount]), Year(Date[Date]) =_max)

[Anonymous]

Hi @amitchandak 

Thank you for the formula. It helps me with another task. 

[amitchandak]

@Anonymous , I was trying to solve the invisible task. 😀 🤔

[Greg_Deckler · Accepted solution]

Perhaps:

Measure = 
  VAR __Year = YEAR(TODAY())
RETURN
  SUMX(FILTER('Table',YEAR([Date]) = __Year - 1),[Column])

[Anonymous]

Hi @Greg_Deckler 

Thank you for the response. It helps and the task has been done.