Forum Discussion
ALL (Column) behavior issue
- 6 years ago
KevinW_SDP , You can try YTD with following method. Prefer a date calendar do not use Date from fact.
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date]) LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year)) Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date])) YTD QTY forced= var _max = today() return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) 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) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max) YTD QTY forced= var _max = maxx('order',[Order date]) return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max1 =maxx('order',[Order date]) var _max = date(year(_max1)-1,month(_max1),day(_max1)) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Hello KevinW_SDP ,
Please share sample data with expected results (including the filters you were applying and not getting the right results).
Ideally Time Intelligence functions should solve your problem.
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
Please read my original question. I have not got to the point of publishing this code because it throws an error in Visual Studio as mentioned at the end of the question. The problem is not the results of the query, but the fact that the documentation states one thing and when coding it, it is throwing an error stating it is not being used properly.