Forum Discussion
Aggregate Count & Sum by Customer Over Time
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
6 Replies
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,
AngeliaThank 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
