Sum of sales for last year

[Morkil]

I'm trying to write a measure that returns the sum of sales for the previous year.

My users need to be able to filter via a report slicer a particular year and potentially month, the result of this should have this measure return the sum of sales for the prior year - ignoring the month filter (i.e., I do not want SAMEPERIODLASTYEAR). 

Another note is that "Sales" is a measure too (not a field/col), as this has already got some calcs over it to filter to particular accounts.

Thanks.

[Morkil · Accepted solution]

I got some help and this managed to solve my issue:

MeausreName =
VAR selectedYear = SELECTEDVALUE('Calendar'[Year])
var PreYear = SelectedYear-1

RETURN
CALCULATE([Sales Amount], FILTER(
ALL('Calendar'),
'Calendar'[Year] = PreYear
))

[MattAllington]

First read my article here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

you do want SAMPERIODLASTYEAR but you need to ignore the month selection/filter. Exact formula will depend on your data, but you need something like this

CALCULATE([Sales existing measure],sameperiodlastyear(calendar[date]),all(calendar[month]))

I haven't tested it

[Morkil]

Hi Matt.

Thanks for your response. I read your article, it was very informative and well written. Thank you.

Underfortunately it hasn't resolved my issue though, whenever I filter on month my measure values change. I just want to see the total for the entire prior year, not for the particular year by month, regardless of what month filter is selected.

Thanks

[MattAllington]

How about you post a sample image of data using Excel to illustrate what you want. 

[Morkil]

Sure. 

Let's say these are the sales by month and year for 2023 on the left. I want a measure that produces what is on the right "Sales" column (I should have called this Sales Last Year). Essentially I want it to ignore the filter context of "Month" and just return the previous years total sales. I've added the "Filtered" columns to show that the user has filtered to the year 2024 (and any month within it) but regardless the measure returns the total sum of sales of the prior year. I do not want the month to add to the filter context, (e.g., filtering to Apr, 2024 should not return Apr 2023's sales value of 266).

I hope this makes sense. Please let me know if it does not.

Thank you.

[MattAllington]

The issue is that the month filter needs to be removed first. I did it at the same time as the SAMEPERIODLASTYEAR function. I suspected this was possible and should have provided an option. 

=
CALCULATE (
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( calendar[date] ) ),
    ALL ( 'Calendar'[MonthName] )
)

You need to use your own column names, etc, of course.