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

Need Help

ruxandraalina

3 years ago

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

Forum Discussion

Need help with DAX