cancel
Showing results 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

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 Name Promo End Date Total Sales 30 days After Promo 1 4/30/2019 \$458,664 Promo 2 8/31/2019 \$262,729 Promo 3 5/31/2020 \$6,488 Promo 4 9/30/2020 \$21,360 21,360 Desired Result Promo Name Promo End Date Total Sales 30 days After Promo 1 4/30/2019 \$458,664 Promo 2 8/31/2019 \$262,729 Promo 3 5/31/2020 \$6,488 Promo 4 9/30/2020 \$21,360 \$749,241
1 ACCEPTED SOLUTION
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!

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

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!

Helper II

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

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors