Help with Dax formula for financial format

[KevinVertrees]

I'm having difficultly with a Dax formula related to some financial data that i'm trying to work with.  The table below is a sample of the data I'm working with.  The table includes a beginning_balance, which reflects the opening year balance for period 1, and then the starting balance for each period.  What i'm trying to achieve is to allow the user to select a date range (say Q1/2020) and have a formula that includes the beginning_balance + Period_Actuals for the first month of the date range, and then include only the period_actuals for the dates after the first date.  The formula I've developed (which isn't working) is

YTD Actual = CALCULATE(SUMX('Financial Balances', if('Financial Balances'[Date]<=[FirstMonth], 'Financial Balances'[Beginning_Balance]+'Financial Balances'[Period_Actuals],'Financial Balances'[Period_Actuals]) ),ALLSELECTED('Calendar'))

Property	Account	Date	Beginning_balance	Period_Actuals	Current Result	Desired Result
1001buck	12050010	1/1/2020	-295	279.12	-15.88	-15.88
1001buck	12050010	2/1/2020	-15.88	-307	-322.88	-307
1001buck	12050010	3/1/2020	-322.88	224	-98.88	224

Any help would be greatly appreciated!

[Icey · Accepted solution]

Hi @KevinVertrees ,

Try this:

YTD Actual =
VAR FirstMonth =
    EOMONTH ( MINX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] ), 0 )
RETURN
    SUMX (
        'Financial Balances',
        IF (
            'Financial Balances'[Date] <= FirstMonth,
            'Financial Balances'[Beginning_balance] + 'Financial Balances'[Period_Actuals],
            'Financial Balances'[Period_Actuals]
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KevinVertrees , Not very clear. But you have few functions that can help

openingbalancemonth,openingbalancequarter, openingbalanceyear, firstnonblankvalue
closingbalancemonth ,closingbalancequarter, closingbalanceyear and lastnonblankvalue

Refer the document how the work.

If these are useful, you can watch video :https://youtu.be/yPQ9UV37LOU

https://youtu.be/6lzYOXI5wfo