Last Year Total

[GGDAC]

I have 5 years sales figures month wise

I used PBI quick measure of Total YTD to calculate year to date figures by month and year

I am using PBi time intelligence for dates My year and month selections are in slicer

Now i want to calculate same period LY and I am struggling with right measure. Please help

AC

[amitchandak · Accepted solution]

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/

[Greg_Deckler · Accepted solution]

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-TIT...

[v-lili6-msft · Accepted solution]

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