Forum Discussion
Last Year Total
- 6 years ago
You can use dateytd and totalytd or trailing measure with calendar date
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)) 2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,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/ - 6 years ago
I thought that should be:
LY = CALCULATE([TotalYTD],SAMEPERIODLASTYEAR(Calendar[Dates]))
Where TotalYTD is your YTD measure.
But you can also always see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008 - 6 years ago
hi GGDAC
For your case, simple way is that use SAMEPERIODLASTYEAR Function to create a measure as below:
SAMEPERIODLASTYEAR = CALCULATE([Total YTD], SAMEPERIODLASTYEAR(‘Calendar’ [Date]))
Please refer to this blog:
https://databear.com/power-bi-dax-sameperiodlastyear-paralellperiod-and-dateadd/
and
https://radacad.com/do-you-need-a-date-dimension
By the way, for your [Total YTD] measure is created by quick measure, it should be like below:
Total YTD =IF(ISFILTERED('Calendar'[Date]),ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),TOTALYTD(SUM('Table'[Goal]), 'Calendar'[Date].[Date]))so I would suggest you adjust it as below:Total YTD = TOTALYTD(SUM('Table'[Goal]), 'Calendar'[Date])Regards,
Lin
- 6 years ago
Thanks Lin
Sameperiodlastyear solved the issue
Thanks once again for the support
hi GGDAC
For your case, simple way is that use SAMEPERIODLASTYEAR Function to create a measure as below:
SAMEPERIODLASTYEAR = CALCULATE([Total YTD], SAMEPERIODLASTYEAR(‘Calendar’ [Date]))
Please refer to this blog:
https://databear.com/power-bi-dax-sameperiodlastyear-paralellperiod-and-dateadd/
and
https://radacad.com/do-you-need-a-date-dimension
By the way, for your [Total YTD] measure is created by quick measure, it should be like below:
Regards,
Lin
Thanks Lin
Sameperiodlastyear solved the issue
Thanks once again for the support