Forum Discussion
Previous Year YTD
Hello All,
I need to calculate sum of Actual values. I have column "Actual". I am using two cards to show the sum of Actual. Actual card displaying sum for the curren fiscal year. say From nov2020 to till date. and prior year card displaying sum for previous fiscal year say nov2019 to oct2020. Rigtnow I have passed the filter to cards "fiscal Year" and selected 2021 for Actual to show current year data and filtered 2020 to show prior year data in card. But I need to do this dynamic. coz every time need to change the filter for current year and previous year if the more years data get added in future.
How make this dynamic? can you suggest me how to so this using DAX?
Hi Anonymous ,
Try to change the ""12/31" to "10/31" in your scenario, which defines the year-end date.
Reference: DATESYTD function (DAX) - DAX | Microsoft Docs
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous ,
Have you tried Time intelligence
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))Or
YTD=
var _min = date(year(today()),1,1)
var _day = datediff(_min, today(),day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] =year(Today()) && 'Date'[Day of Year] <= _day) )LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _day))Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uAThanks amitchandak ,
I tried with above dax but not getting correct solution
See values using 1st DAX . I think its calculating sum from nov-2019 till feb 2020. I need till october2020
- Icey
Community Support
Hi Anonymous ,
Try to change the ""12/31" to "10/31" in your scenario, which defines the year-end date.
Reference: DATESYTD function (DAX) - DAX | Microsoft Docs
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
