Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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)
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)
@EmaVasileva , I was trying to solve the invisible task. 😀 🤔
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |