Forum Discussion
Help with dates table and count
I want to merge a historical data table with live data imported from a database. The historical data includes data before 1st June 2019. The live data is after this date.
I created a dates table and created a relationship in the models tab between the dates column in the dates table and the dates columns in the live and historical data.
When creating a table with the dates table date and counting the relevant values in the historical/live data, I only get the total count in each row. Whereas when I use the dates column in either live/historical data, the count is broken down by date
(e.g. with historical data count and historical data date field: July 1st = 10, July 2nd = 5, Total = 15...but if i use the dates table date i get July 1st = 15, July 2nd = 15, Total = 15)
The date formats are exactly the same. ANy ideas how to overcome this?
2 Replies
Anonymous ,
There can be a few reasons
- You do not have all dates in calendar table.
- You may have a timestamp in your date and it is not visible because of the datatype date or the format that does not include time. Change the datatype to datetime and have a format that shows time to check.
- In above case, if you have date with timestamp. Create a new date column and join it with date calendar.
Sales Date 1= [Sales Date].Date Sales Date 2= Date(Year([Sales Date]), Month([Sales Date]), Day([Sales Date]))Hi Anonymous ,
What is the formula are you using? What is the relationship between your fact table and date table?
Best Regards,
Dedmon Dai
