Forum Discussion
Time Intelligence with Monthly Data Model
Sounds like the right approach.
Agree, I routinely create models with monthly transactional data, and simply follow a first-of-the-month or last-of-the-month date convention with dates in the fact table (can't see any reason to prefer one over the other), related to a standard daily calendar table.
As long as reports don't filter below the month level, it works just fine with time intelligence functions.
There are methods to allocate monthly data to days (some ideas e.g. here) but there is no point in doing this if your underlying granularity is monthly.
What about when you have a mixe of date detail and Period summary?
Hi Anonymous
Do you mean that your fact table contains values at both a date level and totals for periods, effectively double-counting?
I would generally say include one or the other, but not both (depending on reporting needs).
Alternatively, an aggregated fact table could be included separately from the detailed fact table (similar concept to aggregations but without necessarily using DirectQuery for the detailed table). Then measures could be written to reference the appropriate table.