cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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