cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Return Previous Month's Sales based on whether item is promoted during current month

Hi all,

 

I am trying to create a measure to return previous month's sales for an item that is on promotion during the current month. I created a dummy data set below that illustrates what I am trying to achieve. The promotion column indicates 0 if it is not on promotion and 1 if it is on Promotion. 

 

ItemDatePromotion IndicatorSales
TV 12/1/20200$50
TV 12/2/20200$100
TV 12/3/20200$80
TV 13/1/20201$150
TV 13/2/20201$200
TV 13/3/20201$175
TV 22/1/20201$120
TV 22/2/20201$130
TV 22/3/20201$115
TV 23/1/20200$70
TV 23/2/20200$50
TV 23/3/20200$60

 

Desired Result:

 Slicer:March
   
ItemPromotion SalesPrevious Period Sales
TV 1$525$230

 

The main challenge is that if I create a calculate function that filters on the promotion indicator, I end up with something like this (which is not desireable):

 Slicer:March
   
ItemPromotion SalesPrevious Period Sales
TV 1$525$0
TV 2$0$365

 

Current dax:

Previous Period Sales = calculate(sum(Sales), Promotion Indicator = 1, previousmonth (date))

 

Thank you so much for your help!

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Return Previous Month's Sales based on whether item is promoted during current month

Generally need a separte date table to get time intelligence functions to actually work properly. 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...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Return Previous Month's Sales based on whether item is promoted during current month

Greg,

 

Thanks for the reply. In my actual pbix I do have a separate date table that is used for filtering. For simplicity, I didn't indicate it above. Yet, the date system doesn't seem to be the problem.

Highlighted
Anonymous
Not applicable

Re: Return Previous Month's Sales based on whether item is promoted during current month

Your measure must be:

PP Sales =
if( salectedvalue( T[Promotion Indicator] ) = 1,
	CALCULATE(
		sum( Sales[Amount] ),
		PREVIOUSMONTH( Calendar[Date] )
	)
)


Best
D

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021