cancel
Showing results for
Did you mean:
Helper I

## Last year same month with slicer

I'm trying to get my sales for the same month last year but specific to the slicer. When I use this formula:

Last Year Same Month =
CALCULATE (
SUM ('Sell In'[Sales]),
FILTER (
ALLSELECTED ( 'Sell In'),
YEAR ( 'Sell In'[Month].[Date] )
= YEAR ( MAX ( 'Sell Out'[Date].[Date] ) ) - 1
&& MONTH ( 'Sell In'[Month].[Date]) = MONTH ( MAX ( 'Sell Out'[Date] ) )
)
)
I get a blank. If I edit the interactions and make it independent of the slicer (with my date) it ends up working perfectly. But then if I choose a different date, it won't update.

5 REPLIES 5
Community Support

## Re: Last year same month with slicer

HI @Pete3p0 ,

I'd like some sample data to help us clarify your scenario.

BTW, you can also try to remove suffix and use original date fields as condition for calculate:

Last Year Same Month =
CALCULATE (
SUM ( 'Sell In'[Sales] ),
FILTER (
ALLSELECTED ( 'Sell In' ),
YEAR ( 'Sell In'[Month] )
= YEAR ( MAX ( 'Sell Out'[Date] ) ) - 1
&& MONTH ( 'Sell In'[Month] ) = MONTH ( MAX ( 'Sell Out'[Date] ) )
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Helper I

## Re: Last year same month with slicer

Is there no way to create a column that calculates the previous year's same month column? So I can create a column where it shows the previous year's month but then want something like this:

And the do a sumifs formula to get the sales for each product:

Community Support

## Re: Last year same month with slicer

HI @Pete3p0 ,

You can try to use following calculated column formula if it works for your requirement:

Last Year Same Month =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Month] )
= YEAR ( EARLIER ( 'Table'[Month] ) ) - 1
&& MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
&& Table[Product] = EARLIER ( Table[Product] )
)
)

If above not help, please share a pbix file with some sample data for test.

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Helper I

## Re: Last year same month with slicer

Hi,

When I applied the formula, it gave me a figure much higher than anticipated. I realised I also needed to include retailer. I tried to do that:

Last Year Same Month =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Month] )
= YEAR ( EARLIER ( 'Table'[Month] ) ) - 1
&& MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
&& Table[Product] = EARLIER ( Table[Product] )
&& Table[Retailer] = EARLIER ( Table[Retailer] )
)
)

it gives me the following error:

"Cannot nest EARLIER/EARLIEST functions."

Thanks

Community Support

## Re: Last year same month with slicer

HI @Pete3p0 ,

If you can please share us a sample pbix file for test, it will help for test to modify dax formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors