cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors