Measure for summarizing sales by various starting dates but sum of the measures wrong

[evolve_Tricia]

Hi, 

I have calculated the sales after unique permotions with certain discount_name (discounts can be repeatedly used). Here is the DAX for calculating sales within 48 hours after each promotion which summarized the revenue from "orders" table.  

Sales 48h = 
VAR date_online = MAX(performance[Date])
RETURN 
CALCULATE(
    SUM(orders[revenue]),
    DATESBETWEEN(orders[date],  date_online, date_online + 2)
)

Here is the table "performance"

However, when I sum "sales 48 hours" , the total was wrong. Obviously it has taken the max dates of that month/quarter for calculating sums. For example, the total "sales 48h" for 2020-01 should be (982.38+9484.75+5951.44)=16418.57 not 32,303.20. But I tried modifying the DAX, none of them work 😑. Probably a separate measure calculated on a monthly basis is needed? 

Any input is appreciated. 

[ryan_mayu · Accepted solution]

@evolve_Tricia 

have you tried

measure= sumx(value('performance'[date], Sales 48h)

[evolve_Tricia]

@ryan_mayu 
Thank you for your response. I followed your logic and corrected my DAX as

SUMX(VALUES(performance[Date]), performance[Sales 48h]))

And it worked like a charm!!!! Thanks!  

[ryan_mayu]

@evolve_Tricia 

You are welcome.

[amitchandak]

@evolve_Tricia , try like

Sales 48h =
VAR date_online = MAX(performance[Date])
RETURN
sumx(summarize( orders,orders[date],orders[promotion] ,"_1"
CALCULATE(
SUM(orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2)
)),[_1])

Sales 48h =
VAR date_online = MAX(performance[Date])
RETURN
sumx(values( orders[date])
CALCULATE(
SUM(orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2)
))

[amitchandak]

@evolve_Tricia , Try like

Sales 48h =
VAR date_online = MAX(performance[Date])
RETURN
CALCULATE(
SUM(orders[revenue]),values(orders[date]), values(orders[promotion]),
DATESBETWEEN(orders[date], date_online, date_online + 2)
)

Sales 48h =
VAR date_online = MAX(performance[Date])
RETURN
sumx( orders,orders[date],orders[promotion] ,"_1"
CALCULATE(
SUM(orders[revenue]),
DATESBETWEEN(orders[date], date_online, date_online + 2)
),[_1])

[evolve_Tricia]

@amitchandak 
Thanks for your prompt respons.

I got the same result from your first DAX. The second one didn't work because orders does not contains "promotion" column. But even if I use performance[promotions], the numbers were not the total of sales 48hr either