Forum Discussion
Help with Dax formula for financial format
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
| 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!
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.
2 Replies
- amitchandak
Super User
KevinVertrees , Not very clear. But you have few functions that can help
openingbalancemonth,openingbalancequarter, openingbalanceyear, firstnonblankvalue
closingbalancemonth ,closingbalancequarter, closingbalanceyear and lastnonblankvalueRefer the document how the work.
If these are useful, you can watch video :https://youtu.be/yPQ9UV37LOU
- Icey
Community Support
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.
