Forum Discussion
Solved
Need help with DAX
I need to create a column to calculate the total quantity ordered in the past 365 days, for the same item, same customer.
In excel i can do it with Sumifs (screenshot below), but I am struggling to do the same in Power BI.
Can someone please help me with this?
Thanks in advance!
| Order Date | Customer | Item | Quantity | Past year demand |
| 03-Jan-21 | C001 | A | 10 | 0 |
| 07-Mar-21 | C001 | A | 5 | 10 |
| 09-May-21 | C001 | A | 30 | 15 |
| 03-Apr-22 | C001 | A | 10 | 30 |
| 07-Jun-22 | C001 | A | 5 | 10 |
| 09-Jul-22 | C001 | A | 30 | 15 |
| 05-Feb-21 | C002 | A | 20 | 0 |
| 11-Jul-21 | C002 | A | 50 | 20 |
| 05-Jan-22 | C002 | A | 20 | 70 |
| 11-Jun-22 | C002 | A | 50 | 70 |
| 06-Mar-21 | C001 | B | 30 | 0 |
| 08-Apr-21 | C001 | B | 20 | 30 |
| 06-Feb-22 | C001 | B | 30 | 50 |
| 08-Apr-22 | C001 | B | 20 | 30 |
| 04-Feb-21 | C002 | B | 15 | 0 |
| 10-Jun-21 | C002 | B | 40 | 15 |
| 04-Jan-22 | C002 | B | 15 | 55 |
| 10-May-22 | C002 | B | 40 | 55 |
Hello,
*Sheet1 is your table name
Past year demand DAX = var ctx_max_date = max(Sheet1[Order Date]) var ctx_prev_date = date(year(ctx_max_date), month(ctx_max_date), day(ctx_max_date)-365) return if( isblank(CALCULATE(sum(Sheet1[Quantity]), filter(ALLEXCEPT(Sheet1, Sheet1[Customer], Sheet1[Item]), Sheet1[Order Date] < ctx_max_date && Sheet1[Order Date] >= ctx_prev_date))), 0, CALCULATE(sum(Sheet1[Quantity]), filter(ALLEXCEPT(Sheet1, Sheet1[Customer], Sheet1[Item]), Sheet1[Order Date] < ctx_max_date && Sheet1[Order Date] >= ctx_prev_date)))
4 Replies
Hello,
*Sheet1 is your table name
Past year demand DAX = var ctx_max_date = max(Sheet1[Order Date]) var ctx_prev_date = date(year(ctx_max_date), month(ctx_max_date), day(ctx_max_date)-365) return if( isblank(CALCULATE(sum(Sheet1[Quantity]), filter(ALLEXCEPT(Sheet1, Sheet1[Customer], Sheet1[Item]), Sheet1[Order Date] < ctx_max_date && Sheet1[Order Date] >= ctx_prev_date))), 0, CALCULATE(sum(Sheet1[Quantity]), filter(ALLEXCEPT(Sheet1, Sheet1[Customer], Sheet1[Item]), Sheet1[Order Date] < ctx_max_date && Sheet1[Order Date] >= ctx_prev_date)))Hello, thank you for your help!
I used the same DAX formula but got the following results instead.
Could you please advise?
Thank you!
Hello!
You need to create a measure, not a column. Sorry, I forgot to mention this.
