Forum Discussion
Year formula - dynamically changing
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.
Perhaps:
Measure = VAR __Year = YEAR(TODAY()) RETURN SUMX(FILTER('Table',YEAR([Date]) = __Year - 1),[Column])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)
5 Replies
Perhaps:
Measure = VAR __Year = YEAR(TODAY()) RETURN SUMX(FILTER('Table',YEAR([Date]) = __Year - 1),[Column])Hi Greg_Deckler
Thank you for the response. It helps and the task has been done.
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)
Hi amitchandak
Thank you for the formula. It helps me with another task.Anonymous , I was trying to solve the invisible task. 😀 🤔