Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DarylM
Helper II
Helper II

DAX Need Help Calculating total of sales 30 days after each promo

Hello, 

I am having some difficulty calculating the measure total. I have a list of multiple promotions. The goal is to calculate the total sales 30 days after the promo end date. I have the row level working, but the total only shows the row value for the MAX date. 

Any ideas on how I can get this to work? 

 

Current DAX:

Total Sales 30 days =
CALCULATE (
[Total Sales],
DATESINPERIOD (
'Date Table'[Date].[Date],
MAX ( 'Table'[Promo End Date] ),
30,
DAY
),
USERELATIONSHIP ( 'Table'[Promo End Date], 'Date Table'[Date] )
)

 

 

Current Result  
Promo NamePromo End DateTotal Sales 30 days After
Promo 14/30/2019$458,664
Promo 28/31/2019$262,729
Promo 35/31/2020$6,488
Promo 49/30/2020$21,360
  21,360
   
Desired Result  
Promo NamePromo End DateTotal Sales 30 days After
Promo 14/30/2019$458,664
Promo 28/31/2019$262,729
Promo 35/31/2020$6,488
Promo 49/30/2020$21,360
  $749,241
1 ACCEPTED SOLUTION
DarylM
Helper II
Helper II

I figured out a solution for my issue. 

I was able to create another measure and using SUMX referenced the first measure. Thanks!

 

View solution in original post

3 REPLIES 3
DarylM
Helper II
Helper II

I figured out a solution for my issue. 

I was able to create another measure and using SUMX referenced the first measure. Thanks!

 

mahoneypat
Microsoft Employee
Microsoft Employee

Try first calculating your promo date as a variable up front.

 

Current DAX:

Total Sales 30 days = var promodate = MAX('Table'[Promo End Date])
Return CALCULATE (
[Total Sales],
DATESINPERIOD (
'Date Table'[Date].[Date],
promodate,
30,
DAY
),
USERELATIONSHIP ( 'Table'[Promo End Date], 'Date Table'[Date] )
)




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks. I tried that but it still returns the value for the last date and not the Sum. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors