Aggregate Count & Sum by Customer Over Time

[JNTX]

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

[v-huizhn-msft]

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

[JNTX]

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!

[v-huizhn-msft]

Hi @JNTX,

What's calculation rule for the "Previous Week Sales" based on your given data?

Best Regards,
Angelia

[JNTX]

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.

[v-huizhn-msft]

Hi @JNTX,

I mean how to get the column below. What's the calculation rule？



Best Regards,
Angelia

[JNTX]

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