Running Total using SAMEPERIODLASTYEAR

[Niall]

Hi

I have a measure as follows

UniqueInteractionId = DISTINCTCOUNT('Interactions'[Interaction: ID])

I then have two measures to calculate the monthly runnning total for this year and for last year

MonthlyTotalThisYr = 
CALCULATE (
    [UniqueInteractionId], 
    DATESYTD( Dates[Date] )
)

MonthlyTotalLastYr = 
CALCULATE (
    [UniqueInteractionId], 
    SAMEPERIODLASTYEAR ( Dates[Date] )
)

The current year works as expected however the prior year does not

Might anyone have any suggestions as to what i am doing wrong?

Thanks

[lc_finance · Accepted solution]

Hi @Niall ,

The formula SAMEPERIODLASTYEAR takes the same month from the previous year but it does not calculate the YTD. This is why you don't see the correct numbers.

You can try the formula below:

MonthlyTotalLastYr = 
CALCULATE (
    [UniqueInteractionId], 
    DATEADD(DATESYTD(Dates[Date]),-1,Year)
)

The formula first calculates the YTD, then the function DATEADD subtracts one year from the YTD. Therefore you obtain the YTD of the previous year.

Let me know if this fixes your problem.

Regards,

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

[Niall]

Thanks - works perfectly

[lc_finance]

Good to hear!

Best of luck for your analysis

LC