Microsoft Office Tutorials and References
In Depth Information
using FIRSTNONBLANK and LASTNONBLANK Functions
The LASTNONBLANK function you have just seen has a particular behavior, shared also
by FIRSTNONBLANK. The syntax of these functions is the following one:
FIRSTNONBLANK( <column>, <expression> )
LASTNONBLANK( <column>, <expression> )
These functions return the first or last value in <column>, filtered by the current
context, wherein the <expression> is not blank. So these functions behave like SUMX
or similar functions in this regard. They set a row context for a value of <column>
and then evaluate the <expression> by using that row context. If <expression> and
<column> manage data of the same table, everything works fine. However, whenever
<expression> uses columns of tables other than the one to which <column> belongs,
you need to transform a row context into a filter context by using RELATEDTABLE or
CALCULATE. This is a very common situation every time you have a separate Dates
table, which is the best practice for every date-related calculation.
To get the right value for the last nonblank date for a given measure/table, you have to
use something like this:
=LASTNONBLANK( Dates[Date], CALCULATE( COUNT( Balances[Balance] ) ) )
It returns the last date (in the current filter context) for which there are values for the
Balance column in the Balances table. You can also use an equivalent formula:
=LASTNONBLANK( Dates[Date], COUNTROWS( RELATEDTABLE( Sales ) ) )
This formula returns the last date (in the current filter context) for which there is a
related row in the Sales table.
OPENINGBALANCE and CLOSINGBALANCE Functions
DAX provides several functions to get the first and last date of a period (year, quarter, or
month) that are useful whenever you need to get that value of a selection that is smaller than
the whole period considered. For example, looking at the month level (which may be displayed
in rows), you might want to display also the value of the end of the quarter and the end of the
year in the same row, as you can see in Figure 7-53. (The examples shown in this section are
also available in the CH07-12-ClosingBalance.xlsx workbook included on the companion DVD.)
Note Please note that raw data used in this example includes balances for dates through
December 31. For this reason, the DAX function we are going to use provides complete results
because the data based on the LASTDATE function would not work if the last day of a period
(such as month, quarter, or year) were missing.
Search JabSto ::




Custom Search