Non-Contiguous Dates in Fact Tables

[shaykoooo]

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!

[v-shex-msft]

HI @shaykoooo,

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.

In addition, you can also try to use date function to manually setup data range filters, it not need a continuous calendar table.

Time Intelligence "The Hard Way" (TITHW)

Regards,

Xiaoxin Sheng

[adolfoCorreaCos]

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

[LivioLanzo]

@shaykoooo

Time Intelligence will work as long as your Date dimension table has contiguous dates set up and you mark it as a date table. 

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]

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

[LivioLanzo]

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