Home » OPENINGBALANCEMONTH Function DAX

OPENINGBALANCEMONTH Function DAX

OPENINGBALANCEMONTH function is a Power BI time intelligence DAX function which evaluates evaluates the specified expression for the date corresponding to the end of the previous month after applying all the filters.



SYNTAX

OPENINGBALANCEMONTH(<expression>,<dates>[,<filter>])
expression is an expression that returns a scalar value.
dates is a column that contains dates.
filter It is optional. An expression that specifies a filter to apply to the current context.

Lets take a look at an example using a OPENINGBALANCEMONTH function in Power BI.

Here we have two tables named as TranData and Datetable. TranData table contains day wise transactions amount, while Datetable contains a date series, or basically you can say it is calendar table.

And there exists a one to one relationship between these two table on date columns.



Lets create a measure named OBMonth that uses OPENINGBALANCEMONTH function and returns the opening balance for every month.

OBMonth = OPENINGBALANCEMONTH(SUM(AccountData[Balance]), Datetable[Date])
Once you compile the DAX, just drag it into visual to see the output.
You can see, It returns the opening balance for every month.
You will also notice that the opening balance for January 2020 is null, the reason is that the opening balance for any month would be the closing balance of previous month, and in dataset there is no data for December 2019 that’s why for January 2020 the opening balance is null.



Lets see scroll down on visual and see the opening balance for February 2020, and March 2020.

You can see the opening balance for February 2020 is 2,683 which is the closing balance of January 2020, while the opening balance for March 2020 is 95,955 which is the closing balance of February 2020.

Lets crate one more visual in report page and take only month and year this time instead of Date, in order to see the opening balance for every month clearly without scrolling down on visual.

Now you can see the visual on the left side of report, it display the opening balance for every month.

You can also notice that for January 2020 it is null, and the reason for that we have already seen above.

For February 2020 it is 2,683, and so on..

Also Read..

CLOSINGBALANCEMONTH

SAMEPERIODLASTYEAR

TOTALYTD

LASTNONBLANK



SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 778 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.