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 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
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |