Useful filters for your time dimension (DAX / Tabular)

It is always nice to have some time filters in your Tabular model. Because of reuse I try to create most of these filters in the data source. But if that is not an option you can off course add them directly to your Tabular model. So here are some useful samples.

The first one will give you a flag on the last date with sales.

First lets create a new measure.

Last Sales Date:=LASTNONBLANK('Date'[Date]; CALCULATE(SUM('Internet Sales'[Sales Amount])))

And then add a new column in the date dimension. I my sample I will call this column “Last Date With Sales”

=IF('Date'[Date] = calculate('Date'[Last Sales Date];ALL('Date'));1;0)

So if you need the last date with sales in your report you can add the dimension attribute “Last Date With Sales” and then filter the value to 1. This is quite handy if you are using date functions such as TotalYTD or TotalMTD. Please note that you can use the FIRSTNONBLANK functions to. This will return the first date with sales.

The next flag will be a flag that indicates tomorrows date.

To make this flag I will create a new column and add the following expression

=IF('Date'[Date] = TODAY()-1;1;0)

To test this in AdventureWork you first have to figure out how many days to subtract. This can be done by using the following SQL

select datediff(day, '2010/01/02', getdate())

And then change your expression to something like this

=IF('Date'[Date] = TODAY()-1983;1;0)

To get the current month you can add a column like this

=IF(Month('Date'[Date]) = MONTH(TODAY()-1) && YEAR('Date'[Date]) = YEAR(TODAY()-1);1;0)

And to get the current year you can add a column like this

=IF(YEAR('Date'[Date]) = YEAR(TODAY()-1);1;0)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s