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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors