Time Filters & DAX

[aurias]

Hello,

I am having difficulties creating a dynamic date filter that allows my users to filter dates dynamically by WTD/MTD/YTD. I have a date table but am not using Time Intelligence functions as we are on a fiscal calendar.

My approach is not the most elegant, but this is my first go around with Power BI. I am using a SQL view to create "flags" as identifiers for these date ranges. I then have a formula using nested if statements to create a "Flag" that states the highest tier timeperiod it is in.

This makes it so WTD does not include TD (today), MTD does not include WTD & TD, etc. To solve this, I created a table that lists each flag and the other flags that should be contained within itself. That is then connected to a final table, which lists my date ranges and a column to sort by.

This all works perfectly, but it breaks my LY formulas, which is below:

LY Sales $ = 
VAR MAXDATE = LOOKUPVALUE('Timeframe'[LY_date], 'Timeframe'[Date], MAX('Customer Transactions'[Date]))
VAR DATES = CALCULATETABLE(VALUES('Timeframe'[LY_date]),'Timeframe'[LY_date]<=MAXDATE)
RETURN
CALCULATE(SUM('Customer Transactions'[retail]), FILTER(ALLEXCEPT('Customer Transactions','Location'), 'Customer Transactions'[Date] IN DATES))

This formula returns LY sales correctly when filtering by date/month/year/etc, but not when I use the WTD/MTD/YTD flags.

Does anyone have any insight as to why filtering this way (consolidating groups through related tables) might effect the above formula?

[Anonymous]

@aurias , is your fiscal calendar a standard month end, or a 445/454/544 variant?

either one can be solved with some relatively simple DAX, but the 445 variants are a little tougher.

For example, my company's fiscal year starts on 10/1, and Fiscal October always ends on 10/31.  Year end is alway 9/30.

The following solution will work even with a 445, but you'll need to tweak the weeks a little.

To start, let's just consider MTD and YTD.  You'll need a column in your calendar table called MonthID.  It will be a serial # for each month, always increasing.

Let's say your fiscal calendar is like mine, and starts in October.  First year of your calendar table is 2015.

Oct 2015 will have MonthID = 1

Nov 2015 will have MonthID = 2

Dec 2015 will have MonthID = 3...

Oct 2016 = 13

Nov 2016 = 14     Got it?

You can create this table in M with the following code:

Add_MonthID = Table.AddColumn(<Previous_Step>, "MonthID", each 
    ( [Year] - List.Min(<Previous_Step>[Year]) ) * 12 + [Month #], Int64.Type)

This column needs 2 other fields in your calendar table to be calculated properly.

1. Year, in a numerical format
2. Month #, in a numerical format.  This will be your sort order for months.  In my case, October = 1, November = 2, September = 12

Once you have this calendar, here's the DAX that will give you MTD and YTD for your fiscal calendar:

[Sales] = 
SUM(TableName[ColumnName])

Sales YTD = 
VAR CurrentYear = MAX(CalendarTable[Year])
VAR CurrentMonth = MAX(CalendarTable[MonthID]) 
RETURN
CALCULATE(
	[Sales]
	,FILTER(
		ALL(CalendarTable)
		,CalendarTable[Year] = CurrentYear
			&& CalendarTable[MonthID] <= CurrentMonth
	)
)

Sales MTD = 
VAR CurrentYear = MAX(CalendarTable[Year])
VAR CurrentMonth = MAX(CalendarTable[MonthID]) 
RETURN
CALCULATE(
	[Sales]
	,FILTER(
		ALL(CalendarTable)
		,CalendarTable[Year] = CurrentYear
			&& CalendarTable[MonthID] = CurrentMonth
	)
)

Sales YTD Prior= 
VAR CurrentYear = MAX(CalendarTable[Year])
VAR PriorYear = CurrentYear - 1
VAR CurrentMonth = MAX(CalendarTable[MonthID]) 
RETURN
CALCULATE(
	[Sales]
	,FILTER(
		ALL(CalendarTable)
		,CalendarTable[Year] = PriorYear
			&& CalendarTable[MonthID] <= CurrentMonth
	)
)

I don't do much with WTD, but you can build a similar WeekID column and do the same pattern.

Essentially, you remove all filters from your calendar, and rebuild the filters that you need based on the values captured from the current filter context that were stored as variables.  This filtered table then gets passed into the CALCULATE() statement of the measure.

Hope this helps,

~ Chris