Help with dates table and count

[Anonymous]

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?

[v-deddai1-msft]

Hi @Anonymous ,

What is the formula are you using? What is the relationship between your fact table and date table?

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous ,

There can be a few reasons

1. You do not have all dates in calendar table.
2. 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.
3. 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]))