Dax sumifs .How to convert Excel formula to Dax formula

Question

How to convert Excel formula to Dax formula. I want to find out sum qty of previous day hour 24 if the current hour is 1 .

Below is the example using excel formula. =IF(E3=1,SUMIFS($F$3:$F$17,$C$3:$C$17,D3,$E$3:$E$17,24),0)

date,previous date,hour,qty,Formula

10/10/2016,10/09/2016,24, 5,0
10/11/2016,10/10/2016, 1, 1,8
10/12/2016,10/11/2016,24, 2,0
10/13/2016,10/12/2016,24, 2,0
10/13/2016,10/12/2016, 3, 2,0
10/14/2016,10/13/2016, 1, 2,32
10/14/2016,10/13/2016, 1, 2,32
10/14/2016,10/13/2016,24,10,0
10/14/2016,10/13/2016, 4,10,0
10/14/2016,10/13/2016,24,10,0
10/13/2016,10/12/2016,24,10,0
10/13/2016,10/12/2016, 1,10,2
10/13/2016,10/12/2016,24,10,0
10/13/2016,10/12/2016,24,10,0
10/10/2016,10/09/2016,24, 3,0

Answer 1

Let's say your table is called Table1.

Create a calculated column with this formula:

CALCULATE(SUM(Table1[qty]),FILTER(Table1,[date] = (EARLIER([date]) - 1) && 
Table1[hour]=24 && EARLIER([hour]) = 1))

This will replicate the results you were able to calculate using SUMIFS() in Excel.

Now, you can totally use this formula and run with it, but the slight caveat is that it returns blank instead of 0 for cases where the formula does not apply. In order to handle that, you can use a blank check in DAX, like so:

=var x = 
CALCULATE(SUM([qty]),FILTER(Table1,[date] = (EARLIER([date]) - 1) && Table1[hour]=24 
&& EARLIER([hour]) = 1))
RETURN IF(ISBLANK(x),0,x)

Here's a screen of the results, with the calculated column on the right: