Some Cool DAX inbuilt functions

Today lets learn about some important and powerful DAX functions which will help you a lot in day to day calculations:

1. CALCULATE - Calculate enables you to do a calculation having any condition applied

For e.g. below measure, calculates YTD Sales for the same period last year period

PY Sales : CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(DimDate[Date]))

Below will do calculation with all product names ignoring any filters: Total Sales : CALCULATE([Total Sales], All(products[productname])) 2. Similarly SAMEPERIODLAST is an intitutive function which lets you choose the exact same period as you see this year

• 
  

Basically, it returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

So in the given measure, before the CALCULATE Function does the calculation,  SAMEPERIODLASTYEAR returns the previous year date from DimDate table. Then Calculation for YTD Sales is done for that year.

PY Sales : CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(DimDate[Date]))

3. ISFILTERED() - can check whether a column is being filtered directly or if any of the columns of the table is being filtered directly.

In the below measure, ISFILTERED() will check if there is a value selected/filtered on the year slicer, the calculation is done, else the string is returned. PY sales no year selected wtext = IF(ISFILTERED(DimDate[Year]),     CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(DimDate[Date])),"Select a year in slicer")

4. HASONEVALUE() - Returns true when there’s only one value in the specified column.

5. FIRSTNONBLANK() and LASTNONBLANK() - Returns the first and last value in the column for which the expression has a non blank value respectively.

Calculates the first date which has non blank sales value.

firstnonblank 1 = FIRSTNONBLANK(Sales[Date],SUM(Sales[Sales]))

The below measure calculates, first non blank month which has total sales value

Firstnonblank month = FIRSTNONBLANK(Sales[Month], [Tot sales])

The difference between FIRSTNONBLANK() and FIRSTNONBLANKVALUE()
FIRSTNONBLANK() returns first non blank value of a column, while FIRSTNONBLANKVALUE() returns the first non blank value of an expression. You have to be careful to sort the data on right column and check results.

E.g. gives the first non blank month having the sales 

Firstnonblankvalue year = FIRSTNONBLANKVALUE(Sales[Month], SUM(Sales[Sales]))

Thanks :)