Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@evolve_Tricia ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @amitchandak ,
I have provided a sample data in the begining of my original post.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |