Forum Discussion

Niall's avatar
Niall
Regular Visitor
6 years ago
Solved

Running Total using SAMEPERIODLASTYEAR

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

 

 

 

Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    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

3 Replies

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    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