Hi Anonymous ,
Try this formula. 'Table' is data table and 'date' is date table. I create a year column in the date table.
Measure 2 = CALCULATE(SUM('Table'[value]),FILTER('Table',YEAR('Table'[date])=SELECTEDVALUE('date'[year])-1))
Best Regards,
Jay
Solution SageHi,
Continuous dates are required for all time intelligence calculations. Kindly use the date or calendar table to pass date.
Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Regards,
N V Durga Prasad
Super Usernvprasad , You need to use a date table in all such cases.
refer example
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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
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 :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Hi amitchandak
Dim_Time is my date table. Can you see something wrong with it? What should I check on it?
I converted the DAX formulas to pass Dim_Time and not Dim_AccountingPeriod since Dim_Time has continuous dates, but I get the following:
Hi amitchandak
I used these two of your suggestions for Last Year Sales, but they dont work.
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
What does "12/31" mean? the syntax seems wrong. Visual studio gives an error when used verbatim.