Non-Contiguous Dates in Fact Tables

Question

Hi,Can I still use Time Intelligence DAX functions in a situation like this? Will I need to create a contiguous dates in Date table and then connect it to both of my fact tables? My dates in the fact tables only show the last day of every month, 31/1/2016 through 31/12/2018.Thank you!

liviolanzo · Answer

shaykoooo&nbsp;Time Intelligence will work as long as your Date dimension table has contiguous dates set up and you mark it as a date table.&nbsp;Now if your fact tables represents Snapshot at month end you need to be careful not to sum the data at the year Total but thatrs another topic

shaykoooo · Answer

So basically I can? And I am not sure I followed the second part of your answer, what is the caveat?

liviolanzo · Answer

Time intelligence will work as normal. What I was saying is that if your month end data is a snapshot, for instance Quantity Available as of 31 October, Quantity Available as of 30 November etc then you need to be careful at the year level totals not to sum the quantities of all the months but just use the Quantity as of end of December for instance&nbsp;

anonymous · Answer

HI&nbsp;shaykoooo,
&nbsp;
Yes, I think you may need a continuous calendar table to link each tables, then you can use time intelligence functions to use related calculator table to calculate with current table.
&nbsp;
In addition, you can also try to use date function to manually setup data range filters, it not need a continuous&nbsp;calendar table.
Time Intelligence "The Hard Way" (TITHW) 
&nbsp;
Regards,
Xiaoxin Sheng

anonymous · Answer

2020 and still using DATE() vs time intelligence functions, works for me for date columns tied to each table vs a date table.

Forum Discussion

Non-Contiguous Dates in Fact Tables

Related content

SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

Average Score over time with non-contiguous dates

Adding Non-Contiguous Dates

Substitute for SAMEPERIODLASTYEAR() which will work for non contiguous selections

Sales PY for Non Contiguous date range