cancel
Showing results for
Did you mean:
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.

 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!

3 REPLIES 3
Highlighted
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!

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

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

Best
D

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

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

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

Top Solution Authors
Top Kudoed Authors