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

[p0s01si]

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!

[Anonymous]

Your measure must be:

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

Best
D

[Greg_Deckler]

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...

[p0s01si]

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.