Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |