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.
Hi I am using the working formula below to calculate our YTD turnover.
YTD_2020 = CALCULATE(SUM('FACTVERKOOP_ALL'[BRUTO_OMZET]);YEAR('FACTVERKOOP_ALL'[DATE])=YEAR(TODAY()))
What formula can I use to get the result for the year before?
Many thanks in advance
Solved! Go to Solution.
@BartVlek , With a date table
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
or
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year))), blank())
@BartVlek , With a date table
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
or
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year))), blank())
var maxyear = YEAR(TODAY())
YTD_2020 = CALCULATE(SUM('FACTVERKOOP_ALL'[BRUTO_OMZET]);YEAR('FACTVERKOOP_ALL'[DATE])=maxyear)
and you can use the same substratct one to geet last year
var maxyear = YEAR(TODAY())
LYTD_2019 = CALCULATE(SUM('FACTVERKOOP_ALL'[BRUTO_OMZET]);YEAR('FACTVERKOOP_ALL'[DATE])=maxyear-1)
And to confirm are you looking for Full year else the same dates available in latest year with the last year?
If you think this is helpful please Accept this as Solution and leave a like.
Regards,
Manikumar
Proud to be a Super User!
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |