Forum Discussion
YoY Calculations incomplete Year
Hi,
I have a problem with the calculation of the previous year's change. I used the Quickmeasure to calculate the previous year's change. This allows me to show the rates of change for months and years in the table. The problem is that it also shows rates of change in an incomplete year. The programme calculates the average for January to September 2019 and calculates the rate of change for 2019. I don't want to see any values for an incomplete year.
Hi Anonymous,
For incomplete date field calculation, I'd like to suggest you use date function to manually defined filter range instead of using time intelligence functions:
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
2 Replies
With Date calendar, You can use datesytd or totalytd . Or year behind measure
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete 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))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/Appreciate your Kudos.
Hi Anonymous,
For incomplete date field calculation, I'd like to suggest you use date function to manually defined filter range instead of using time intelligence functions:
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
