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])
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..
4,100 total views, 3 views today