topic Re: Return Previous Month's Sales based on whether item is promoted during current month in DAX Commands and Tips

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

p0s01si — Mon, 23 Mar 2020 15:07:46 GMT

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.

Item	Date	Promotion Indicator	Sales
TV 1	2/1/2020	0	$50
TV 1	2/2/2020	0	$100
TV 1	2/3/2020	0	$80
TV 1	3/1/2020	1	$150
TV 1	3/2/2020	1	$200
TV 1	3/3/2020	1	$175
TV 2	2/1/2020	1	$120
TV 2	2/2/2020	1	$130
TV 2	2/3/2020	1	$115
TV 2	3/1/2020	0	$70
TV 2	3/2/2020	0	$50
TV 2	3/3/2020	0	$60

Desired Result:

	Slicer:	March

Item	Promotion Sales	Previous 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

Item	Promotion Sales	Previous 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!

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

Greg_Deckler — Mon, 23 Mar 2020 15:36:31 GMT

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-TITHW/m-p/434008

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

p0s01si — Mon, 23 Mar 2020 15:46:25 GMT

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.

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

Anonymous — Mon, 23 Mar 2020 21:43:50 GMT

Your measure must be:

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

Best
D