Problems occur then summarizing monthly totals of sales within 1 week by different promotions

[evolve_Tricia]

Hello,

I'm trying to calculate sales after 1 week for different promotions and aggregate them by month and quarter. I had no problem making such measure for individual promotions.

Date	Discount name	Promotion	Sales 1 week
2020/1/11	DANCE	promotion1	2464.74
2020/1/15	happyshopping	promotion2	15811.39
2020/1/23	happyshopping	promotion3	18014.71
2020/2/8	ninoshades	promotion4	20030.41
2020/2/10	happyshopping	promotion5	27346.81
2020/2/20	happyshopping	promotion6	54
2020/2/28	daniil	promotion7	1172.63
2020/3/4	happyshopping	promotion8	22165.05
2020/3/7	friends	promotion9	85.48

Sales 1 week = 
VAR date_online = MAX(promotion[Date])
VAR code =VALUES(promotion[Discount name])
RETURN
    CALCULATE(
      SUMX(FILTER(orders, orders[discount_name] IN code), orders[revenue]),
      DATESBETWEEN(orders[date],  date_online, date_online + 7))

However, when aggretating by month, sales of the promotion at the end of February were calculated less than 1 week after promotion started because the time span has reached to another month. 

Sales 1 week sum = SUMX(VALUES(promotion[Date]), [Sales 1 week])
Sales 1 week by ym = SUMX(VALUES(date_table[year-mon]), [Sales 1 week sum])

The total of 2020-02 should be 56321.25 rather than 38829.28.

When I selected 2020-02, obviously sales of promotion4 within a week were calculated only till the end of February for only 2538.44. Is it possible to adjust the DAX of this measure to cover the complete 1 week sale not affected by months? 

Thanks.

[amitchandak]

@evolve_Tricia ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[evolve_Tricia]

Hi @amitchandak ,

I have provided a sample data in the begining of my original post. 

[V-lianl-msft]

Hi @evolve_Tricia ,

I don't know what is the relationships between your tables , and which fields are used in your visual. What you need to know is that the value of the measure is relevant to the current context.

Try to add filter condition to these measure "Sales 1 week by ym" ,"Sales 1 week sum".

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Please mask any sensitive data before uploading

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.