Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Your measure must be:
PP Sales =
if( salectedvalue( T[Promotion Indicator] ) = 1,
CALCULATE(
sum( Sales[Amount] ),
PREVIOUSMONTH( Calendar[Date] )
)
)
Best
D
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...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |