Global Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Need help in DAX formulas

  • 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 !





    ------------------------------
    Maruthi Siva Prasad
    Data warehouse developer
    Unity
    Charlottenlund
    91654588
    ------------------------------


  • 2.  RE: Need help in DAX formulas

    Bronze Contributor
    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
    ------------------------------