Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thanks - works perfectly
Good to hear!
Best of luck for your analysis
LC
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |