DAX Time intelligence functions

Hi,

Can someone help or point me in the direction of information re time intelligence, and how they work in relation to the 'calendar date' table and the date table coming from a fact or dim table.


I have two very similar measures ;

Please Login or Register  to view this content.

this is using the dats from the sales order date column and seems to work fine with a pivot created using that date table.

this

Please Login or Register  to view this content.

this does not work even though the only thing changed is the number and type of period?
if i re write using calendar dates and create pivot uinng those dates it woks fine, but then I can't use a measure such as ;

Rolling 3 day total:=VAR mdate = MAX(fSales[Order Date])
RETURN
SUMX(FILTER(ALL(fSales[Order Date]),fSales[Order Date]<= mdate &&
fSales[Order Date] > mdate - 3 ),[sSales] ),

In fact I 'd be interested to know if there's a way of writing this using the 'Calendar Date' table. I've attached a very simple workbook, showing the two pivots and the above measures.

Richard.

Thanks, works fine, as you said returns where there are not orders, but it's still correct and you can use an if you only want it to work
where there are values you can finish with an if statement,

Please Login or Register  to view this content.

I need to spend more time on this,

Richard.