Would like to get your opinion on a case that I dealt with recently. I have 1 calendar table and 1 fact table. The fact table is 35M rows long. When doing Dateadd( with -1) the data moved up while when I did (+1) it moved down (the movement should be opposite). Trying it on a shortened dataset with no changes in terms of the links or any other formulas it moved at the right direction. Then today the longer dataset just displayed the correct direction as well. Has anyone ever encountered something similar? Also, any advice of when there is a need to use daily data for 35M rows what tips or tricks there are to make it easier for the model without deleting any data?
- The calendar table has been marked as date table and is linked to the fact table via a 1 to many relationship.
- Granularity of the date table is daily with Date today, name of member, whether membership is active as of date today, membership type and 5 other subtypes.
- Formula that didn’t initially work is =CALCULATE(DISTINCTCOUNT(‘Fact’[Serial Number]),DATEADD(‘Calendar’[Date],-1,YEAR)).
- I realized that for this case specifically, yes data can be folded in the query, but in cases where it can’t be what should be done? Does adding more dimension tables reduce the burden to measures?