# Global Power BI User Group

View Only

## Need help in DAX formulas #### Maruthi Siva PrasadFeb 12, 2019 06:18 AM • #### 1.  Need help in DAX formulas

Posted Feb 12, 2019 06:18 AM
Hi Team,
I have to calculate 4 coumns based on Amount column.

1. Begining Balance Formula in SQL

SUM(CASE WHEN CAST(trans.end_date AS DATE) < '2018-11-01' THEN trans.amount END) AS [Beginning Balance]

-- Above calculation does SUM(Amount) From 2017-01-01 Till 2018-11-01

2. Ending balance Formula in SQL

SUM(CASE WHEN CAST(trans.end_date AS DATE) <= '2018-11-30' THEN trans.amount END) AS [Ending Balance]

-- Above calculation does SUM(Amount) From 2017-01-01 Till <= 2018-11-01

Other 2 columns as per month when I select FILTER Month

3. SUM( CASE
WHEN (CAST(trans.end_date AS DATE) >= '2018-11-01' AND CAST(trans.end_date AS DATE) <= '2018-11-30')
AND trans.type IN ('earnings')
THEN trans.amount
END
) AS [Monthly Earnings]

--Above Formula in SQL
-- Above calculation does SUM(Amount) From 2018-11-01 and 2018-11-30

4.SUM(CASE WHEN (CAST(trans.end_date AS DATE) >= '2018-11-01' AND CAST(trans.end_date AS DATE) <= '2018-11-30')
AND trans.type IN ('bonus') THEN trans.amount END) AS [Monthly Bonuses]

--Above Formula in SQL
-- Above calculation does SUM(Amount) From 2018-11-01 and 2018-11-30

I have a Month Filter, when I select 2018-November, then 1 and 2 calculate on all Amount data since from 2017 year and 3 and 4 calculates only for 2018-November month.

I am able to derive 3 and 4 calculations as per Month selected. But not 1 and 2 calculations.

Could you please suggest any DAX formulas to solve 1 and 2 calculations? Thanks in advance and appreciate help !

------------------------------
Data warehouse developer
Unity
Charlottenlund
91654588
------------------------------

• #### 2.  RE: Need help in DAX formulas

Posted Feb 15, 2019 10:34 AM
In general, the form for:

SUM(CASE WHEN CAST(trans.end_date AS DATE) < '2018-11-01' THEN trans.amount END) AS [Beginning Balance]

in DAX would be something along the lines of:

SUMX(FILTER('Table','Table'[trans.end_date] < DATE(2018,11,1)),[trans.amount END])

------------------------------
Gregory J. Deckler
Director
Fusion Alliance
Westerville OH
------------------------------