CLOSINGBALANCEMONTH function is a Power BI time intelligence DAX function which evaluates the expression at the last date of the month in the current context.
SYNTAX
CLOSINGBALANCEMONTH(<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 CLOSINGBALANCEMONTH 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 CBMonth that uses CLOSINGBALANCEMONTH function and returns the closing balance for every month.
CBMonth = CLOSINGBALANCEMONTH(SUM(AccountData[Balance]), Datetable[Date])
Once you write the DAX, just compile and drag it into report to see the output.
You can see, it returns the closing balance for every month.
[Also Read: OPENINGBALANCEMONTH function DAX]
For January month you can see it returns the last day (on 31st, Jan 2020) balance that is 2,683.
Also you can take a look at the closing balance for February month that is 95,955 which is the last day balance( on 29th, Feb 2020) in February month as shown below.
Lets crate one more visual in report page and take only month and year this time instead of Date, in order to see the closing 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 closing balance for every month.
The total balance for January is 2,267,719 while the closing balance is 2,683 which was the balance at the last day of January 2020, similarly you can see the closing balance for other months as well.
Also Read..
OPENINGBALANCEMONTH function DAX
