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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.