DAX Power BI: ENDOF & OPENING/CLOSING BALANCE — Calculating expressions based on a snapshot of dates
Sometimes, when solving analytical tasks, we need to sum some values not as a sum for some dates, but as a sum for a certain date, i.e. we need to take a snapshot of calculations of this expression on a certain date. This is relevant, for example, for the balance of goods in a warehouse, for calculating the amount of an account in a bank.
If we consider the amount of a bank account, we cannot just add up 2 values for 2 days. It is for this kind of calculation that we need to take a snapshot, we don’t need to calculate the amount for the whole month. We need to take a specific date and “photograph” our value. And for these cases there are specialized functions.
Two types of functions are used for this purpose:
The first type of functions
ENDOFMONTH, NDOFQUARTER, ENDOFYEAR — return the last day of the month, quarter or month.
STARTOFMONTH, STARTOFQUARTER, STARTOFYEAR — return the first day of a month, quarter, or year.
These functions return a specific day. Therefore, they must be inserted into the CALCULATE function, and then we will be able to calculate a certain expression for a specific date.
The second type of functions
CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR — the expression for the last date of the month, quarter, year is calculated.
OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR — the expression is calculated for the first date of the month, quarter or year.
CLOSING or OPENING functions — these functions no longer return a specific date, but they calculate an expression. That is, these functions already contain both the CALCULATE function and the functions of the first type.
Let’s see how these functions work in practice.
Source table:
A measure to calculate the amount:
Let’s create a new measure, and in this measure, let’s calculate the value of sales on the last day of the month. Let’s do it with the help of CALCULATE. We insert the ENDOFMONTH function as a function, and insert the column with dates into it.
As a result, we see the value of sales on the last day of a particular month. All this code writing can be reduced to one function, namely CLOSINGBALANCEMONTH. Inside we put the expression for counting, a column and the third parameter we can pass some filter, but in this case we will not do it.
As you can see, the result remains the same.
So, if you need to calculate some expression for the first or last date, to see the balance at the end of the period, you should use the above functions.
The problem of using these functions is the possibility that there are no orders on the last date of the month. To take this into account, you need to use slightly different constructs, which will be discussed separately.
Don’t forget to subscribe to
and join our Power BI community
