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

[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

[GGDAC]

Thanks Lin

Solution works

[GGDAC · Accepted solution]

Thanks Lin

Sameperiodlastyear solved the issue

Thanks once again for the support

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

[GGDAC]

Thanks Greg

Solution works

[GGDAC]

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]))

[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/

[Daviejoe]

One further thing, I used your DAX to provide me with a total for the whole of the previous year, however, once i move the date filter back to the previous calendar year it will obviously disappear. 

Is there a way of making it dynamic so if i move the date back the measure will do this also?

[Daviejoe]

@amitchandak This answer of yours was exactly what I was looking for.  Thanks again!

[GGDAC]

Thanks Amit

Solution works

[GGDAC]

Dear Amit

I am too new to PBI so probably your solutions was too much for me to understand

Just to explain a bit more to get specific measure

I have slicer showing months and another slicer showing year. I created measure from Quick Measure in PBI on YTD amount

and I am getting my numbers correct whenever I select given period

Now I want to add another measure for Last Year so when I select period month and year, it also show me Last year numbers

So if you could guide me with specific measure on that

[amitchandak]

@GGDAC , check if a couple of solutions given above helps you.

[GGDAC]

Thank you Amit

I will try that as well and revert

[amitchandak]

@GGDAC ,

what you are using for YTD. The same can be use for last year Like I use datesytd for this year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

The for last year I use dateadd to move it a year behind

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))

As you can have non-continuous dates, and time intelligence need continuous dates most of the time we suggest date calendar.

You have few others like sameperiodlastyear, totalytd ,previousyear that can help.

[Anonymous]

Use sameperiodlastyear function.

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Thanks
Pravin