Limit SAMEPERIODLASTYEAR () to current month only

[PiyushBQ]

Hi Guys,

I create a calendar table, created a relationship with the data and used:

LY Number = CALCULATE(SUM('Data Export'[Data]), SAMEPERIODLASTYEAR(Dates[Date])

It's working fine. However, it's showing LY results in future months. How do we limit the results to the latest results?

Please check the screenshot for reference. I basically want -100% to disappear from the table.

Thank you in advance!
Piyush

 

[Anonymous · Accepted solution]

[Total Data] = SUM('Data Export'[Data])

LY Number =
var __lastDate =
	CALCULATE(
		max( FactTable[Date] ),
		All( FactTable)
	)
var __currentPeriodAdjusted =
	FILTER(
		VALUES( Dates[Date] ),
		Dates[Date] <= __lastDate
	)
var __result =
	CALCULATE(
		[Total Data],
		SAMEPERIODLASTYEAR(__currentPeriodAdjusted)
	)
return
	__result

Best

D

[iluvcoding_91]

Many thanks, this worked!

[Greg_Deckler]

Perhaps check out one of the other time intelligence functions, you may find one that is suitable. Or, 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...