Forum Discussion

Sharmi_28's avatar
Sharmi_28
Helper I
2 years ago

Help with SQL subquery to DAX Please

I have following SQL query. Could you please help me on how to check where clause in DAX. Thank SELECT        SUM (ColAmt) FROM Teams T WHERE       (T.DateCol >= FisrtDateofPreviousMonth And T...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

Hi Sharmi_28 , In DAX instead of Where clause Filter function can be used, try below mentioned DAX meausure

 

EVALUATE
VAR FirstDateofPreviousMonth = EOMONTH(TODAY(), -2) + 1
VAR LastDateofPreviousMonth = EOMONTH(TODAY(), -1)

VAR FirstDateofCurrentMonth = EOMONTH(TODAY(), -1) + 1
VAR LastDateofCurrentMonth = EOMONTH(TODAY())

RETURN
FILTER(
VALUES(T[Col1], T[Col2], T[Col3], T[DateCol]),
T[DateCol] >= FirstDateofPreviousMonth
&& T[DateCol] <= LastDateofPreviousMonth
&& NOT (
CALCULATETABLE(
VALUES(P[Col1]),
P[Col1] = T[Col1],
P[DateCol] >= FirstDateofCurrentMonth
&& P[DateCol] <= LastDateofCurrentMonth
)
)
)

 

Please accept as solution if it helps

Sharmi_28's avatar
Sharmi_28
Helper I
2 years ago

Thanks for reply and solution.
I tried to apply your DAX but its not working for me.

I trying something like

Measure =
VAR FirstDateofPreviousMonth = EOMONTH(TODAY(), -2) + 1
VAR LastDateofPreviousMonth = EOMONTH(TODAY(), -1)

VAR FirstDateofCurrentMonth = min(DimCalendar[Date])
VAR LastDateofCurrentMonth = max(DimCalendar[Date])

RETURN
CALCULATE(
Sum(FactTable[Amount]),
FactTable[Date] >= FirstDateofPreviousMonth &&
FactTable[Date] <= LastDateofPreviousMonth
&&
NOT ( FactTable[Code]= FactTable[Code] &&
FactTable[Date] >= FirstDateofCurrentMonth && FactTable[Date] <= LastDateofCurrentMonth
)
)

But its giving error.