Forum Discussion
Get last year's same month average
Hi,
I would like to compare the period selected to its equivalent total average month of last year.
For example:
I am looking at the average sale value between January 1-25 2019 which is 1000$
How can I get the average value for all of January in 2018?
AverageSales =
CALCULATE(
AVERAGE('SalesTable'[Sales])
)
I tried :
PARALLELPERIOD with -1 year in parameters but it gives the entire year period
If I use DATEADD its selecting the same period : 1-25 which is not what I want
What I need is
January 1-31 of 2018
Any ideas?
Give this a try, it should return the entire month from last year even when looking at a portion in the current year.
PYAverage = VAR _Start = MIN ( 'Date'[Date] ) VAR _Dates = DATEADD ( DATESBETWEEN ( 'Date'[Date], EOMONTH ( _Start, -1 ) + 1, EOMONTH ( _Start, 0 ) ), -1, YEAR ) RETURN CALCULATE ( [AverageSales], _Dates )
3 Replies
Give this a try. It assumes you have a calendar table in your model that is marked as the date table.
PYAverage = CALCULATE( [AverageSales], SAMEPERIODLASTYEAR(DATES[Date] ) )Tried that as well didint work, it only gives me the same selected dates period from last year not the entire month. I have a Date table
I was probably thinking of creating variables to manipulated the selected dates from the slicer as my parameters and use the datesinperiod formula but wasn't able to make it.Give this a try, it should return the entire month from last year even when looking at a portion in the current year.
PYAverage = VAR _Start = MIN ( 'Date'[Date] ) VAR _Dates = DATEADD ( DATESBETWEEN ( 'Date'[Date], EOMONTH ( _Start, -1 ) + 1, EOMONTH ( _Start, 0 ) ), -1, YEAR ) RETURN CALCULATE ( [AverageSales], _Dates )
