The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello all!
Been searching the forums for 2 days now trying to find a solution to the below problem I'm attempting to solve for. Thank you in advance for any assistance you can provide.
1 Table Data source. Data is as follows:
Customer Number - e.g. 123456
Date
$ Amount of Sale
Product Type
What I am attempting to do is show a detail Table in Power BI with the below attributes, by customer, by day:
Customer Number
Date
$ Amount of Sale
Product Type
Total Count of Sale that Day
Total Sum of $ Sale that Day
Total Count of Sale in Today()-6
Total Sum of $ Sale in Today()-6
Hi @JNTX,
Please create measure using the formulas below. Then create a table visual, select [$ Amount of Sale], [Product Type] and the four measure as fields.
Total Count of Sale = COUNT ( Table[$ Amount of Sale] ) Total Sum of $ Sale = SUM ( Table[$ Amount of Sale] ) Total Count of Sale in Today()-6 = CALCULATE ( COUNT ( Table[$ Amount of Sale] ), FILTER ( Table, Table[date] ) = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 6 ) ) Total Sum of $ Sale in Today()-6 = CALCULATE ( SUM ( Table[$ Amount of Sale] ), FILTER ( Table, Table[date] ) = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 6 ) )
Best Regards,
Angelia
Thank you Angelia, I appologize if I wasn't clear in my original post. The Today()-6 was supposed to be a "in the previous week" calculation. I've been able to get the sums for individual days, but not the running total for the previous 7 days.
Example, I'm having trouble finding solutions for the "Previous Week Sales" aggregation piece, by customer, by product:
Customer | Product | Date | Sale | Current Day Sales | Previous Week Sales |
1 | A | 1/1/2017 | $100 | $300 | $300 |
1 | A | 1/1/2017 | $200 | $300 | $300 |
1 | A | 1/3/2017 | $100 | $100 | $400 |
1 | B | 1/3/2017 | $200 | $200 | $200 |
1 | A | 1/5/2017 | $200 | $200 | $600 |
Thank you in advance!
Hi @JNTX,
What's calculation rule for the "Previous Week Sales" based on your given data?
Best Regards,
Angelia
I appologize, I don't understand your question. I'm trying to figure out how to do the sum calculation for the previous week based on customer and product. Seen above in the mockup.
Hi @JNTX,
I mean how to get the column below. What's the calculation rule?
Best Regards,
Angelia
I don't know the DAX of it but it's:
sum(sales)
where date <= date and date >= date-7 days
group by customer, product
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |