Dax for sum if

[SSWADOOD]

How can i convert this sql as dax 

SUM(DECODE (debit_credit_flag,'D', transaction_amount * -1,transaction_amount)) AS "COLLECTED"

TRUNC(TRANSACTION_DATE) BETWEEN :SDATE AND :EDATE

i tried doing the following and failed to get the result

COLLECTED = VAR A = CALCULATE(SUM ( COLLECTED [TRANSACTION_AMOUNT] )*-1,

     FILTER(COLLECTED,COLLECTED[DEBIT_CREDIT_FLAG] = "D" && DATESBETWEEN(COLLECTED [TRANSACTION_DATE],[SDATE],[EDATE])))

VAR B = CALCULATE(SUM ( COLLECTED [TRANSACTION_AMOUNT] ),FILTER(COLLECTED,COLLECTED[DEBIT_CREDIT_FLAG] = "D" && DATESBETWEEN(COLLECTED[TRANSACTION_DATE],[SDATE],[EDATE])))

RETURN

CALCULATE(

FILTER(COLLECTED,IF(COLLECTED[DEBIT_CREDIT_FLAG] = "D",A,B)))

Regards,

ssw

[Anonymous · Accepted solution]

[Net Transaction Amount] =
var TotalDebit = 
	CALCULATE(
		// T is the name of your table
		sum( T[Transaction_Amount] ),
		KEEPFILTERS( T[DC_Flag] = "D" )
	)
var TotalCredit =
	CALCULATE(
		sum( T[Transaction_Amount] ),
		KEEPFILTERS( T[DC_Flag] = "C" )
	)
var Result =
	TotalCredit - TotalDebit
RETURN
	Result

This is the measure that you drop on your visuals to get the net amount for any set of filters.

[Greg_Deckler]

@SSWADOOD Maybe SUMX with a FILTER of >= start date && <= end date

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[SSWADOOD]

@Greg_Deckler 

so here is the table 

as you can see there are different amounts debited and credited against a policy no on various dates 

what i required was to sum the transaction amount of a policy.

all total amount with flag "D" will be subtracted with the total amount witch flag "C"

between selected dates 

POLICY_NO	TRANSACTION_DATE	DC_FLAG	TRANSACTION_AMOUNT
EM/13-000026-07	10-Jul-2020	C	700000
EM/13-000026-07	24-Jul-2020	C	109957
EM/13-000026-07	22-Oct-2020	C	200000
EM/13-000026-07	17-Nov-2020	C	150000
EM/13-000026-07	02-Feb-2021	C	250000
EM/13-000026-07	23-Feb-2021	C	139889
EM/13-000026-07	01-Jan-2021	D	100000

My result should be 

POLICY_NO	TRANSACTION_AMOUNT
EM/13-000026-07	289889

 considering only the orange text as start date, end date was 1-jan-2021 and  30-feb-2021

eventually i did figured it out, but my measure was taking the dates between in consideration 

collected =

var a = calculate

(sum('collected'[transaction_amount])*-1,

filter(collected,collected[dc_flag="d"))

var b= calculate

(sum('collected'[transaction_amount])+0,

filter(collected,collected[dc_flag]="c"))

return

calculate(a+b)

i added the datesbetween funtion in the both variables but its tatking too long to load 

var a = calculate

(sum('collected'[transaction_amount])*-1,

filter(collected,collected[dc_flag="d"),

datesbetween(collected[transaction_date],[sdate],[edate]))

[sdate] & [edate] are the min &  max  dates selected from my  date slicer

[Greg_Deckler]

@SSWADOOD I wouldn't use DATESBETWEEN but maybe just < and > filters && together?