Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning,
I have the table as below, and I tried to calculate the week to date sum for quantity.
The slicers for the table are:
Week
Product Category
Product Sub-category
I like to see when a user click on any specific week/weeks, week to date will sum from week 27 to the max of the selected week/weeks.
I have tried the below DAX:
Week to Date Qty = Calculate(
sum(Table[Quantity]),
Filter(All('Table'), 'Table'[Week] <= max('Table'[Week])))
but it doesn't filter on Product Category or Product Sub-category. I think it's the issue about that All('Table') part.
Could any one show me the correct Dax?
many thanks in advance.
Week | Product Category | Product Sub-category | Quantity |
27 | A | A1 | 5 |
27 | B | B1 | 2 |
27 | A | A2 | 1 |
27 | C | C1 | 2 |
27 | C | C2 | 4 |
28 | B | B2 | 3 |
28 | B | B1 | 1 |
28 | C | C1 | 5 |
28 | A | A1 | 2 |
28 | C | C2 | 2 |
28 | A | A1 | 3 |
28 | B | B2 | 5 |
28 | B | B1 | 3 |
29 | A | A2 | 1 |
29 | C | C1 | 2 |
29 | A | A1 | 2 |
30 | A | A2 | 3 |
30 | A | A2 | 1 |
30 | B | B2 | 5 |
30 | C | C1 | 2 |
30 | B | B1 | 2 |
30 | A | A1 | 1 |
30 | C | C2 | 3 |
30 | A | A1 | 2 |
Solved! Go to Solution.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, thanks so much for your reply.
The graph looks all good if the user highlight the weeks from the first week 27.
Howerver, if a user pick week 28, I like the Week to Date stays the same as 38, instead of 24.
Which means the Week to date number is always the sum of Wk27 to the max Wk that's been selected.
Thanks in advance 🙂
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks so much for your help 🙂
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |