Need help with DAX

[HoshiWS]

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

[ruxandraalina · Accepted solution]

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)))

[HoshiWS]

Hello, thank you for your help!

I used the same DAX formula but got the following results instead.

Could you please advise?

Thank you!

[ruxandraalina]

Hello!

You need to create a measure, not a column. Sorry, I forgot to mention this. 

[PowerUserR]

Hi,

try this: 

PYD =

CALCULATE (

    SUM ( 'Table (3)'[Quantity] ),

    ALL ( 'Table (3)' ),

    'Table (3)'[Order Date] < EARLIER('Table (3)'[Order Date])&&

    'Table (3)'[Customer] = EARLIER ( 'Table (3)'[Customer] ) &&

    'Table (3)'[Item] = EARLIER ( 'Table (3)'[Item]

))