Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
User | Count |
---|---|
123 | |
77 | |
62 | |
50 | |
48 |
User | Count |
---|---|
175 | |
125 | |
61 | |
60 | |
58 |