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
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
- GGDAC6 years agoHelper I
Thank you Greg
My model is something similar to what you sent in attachment. I am new to this and I am trying to develop measure as suggested but it shows error. Please see below my measure and advise where I am going wrong.
Here RE SALE YTD is quick measure taken from PBI which is basically my Sales figures.
Cal.year / month is my calendar table in the field
LY Same Period = CALCULATE('GS Customer'[RE SALE YTD],SAMEPERIODLASTYEAR('GS Customer'[Cal. year / month])) - GGDAC6 years agoHelper I
Thanks Greg
Solution works