Time Filters & DAX

Anonymous , 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