topic Re: Dax for sum if in DAX Commands and Tips

Dax for sum if

SSWADOOD — Mon, 19 Jul 2021 11:52:33 GMT

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

Re: Dax for sum if

Greg_Deckler — Mon, 19 Jul 2021 13:07:19 GMT

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.

Re: Dax for sum if

SSWADOOD — Tue, 20 Jul 2021 11:54:50 GMT

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

Re: Dax for sum if

Anonymous — Tue, 20 Jul 2021 17:07:33 GMT

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

Re: Dax for sum if

Greg_Deckler — Tue, 20 Jul 2021 17:32:54 GMT

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