Date table connection cause wrong average calculation

[ohjjang]

Hi All, 

I am newbie for power bi, and stuck in trouble. 

My data is based on time stamp (every 15 mins), and values from sensors as like below. 

When i put value just on Card, and select average value results coming with 3.79 M, i assume all average value from (1st July to 31 July), but when i put slicer with date with setting 1/7/2022 to 31/7/2022 the average value is changing as like below to 3.76. 

I hope to get 3.79 value because when i do average at excel, could get this number instead 3.76. 

I guess date filter effect on my report somehow but do not know exact reason...

I have tried various things to figure out recreate date table, and using calculate with values etc, i could not figure out. 

Could you please help newbie? 

with slicer based on date

[Greg_Deckler]

@ohjjang My guess, and this is only a guess, is that setting the slicer sets the end time to be the date and 0 time (midnight) and thus excludes an entire day's worth of data?

[ohjjang]

@Greg_Deckler Thank you Greg, 

I did slicer setting 'between' and, only could choice date as correct... do not know how to choice even time for selecting midnight. I just add some more screen captures for your consideration. Thanks again for your help. 

[Greg_Deckler]

@ohjjang Oh, I think I see the problem now (maybe). So what if you use Column1 in your slicer instead of your Date column from your Date table? And that's really strange if you have a Date table with a 1:1 relationship with a column that contains date/time values. 

[ohjjang]

@Greg_Deckler Hi again, Thanks a lot for your advice. 

Yes your approach is work when i use column1 on slicer.

But for me, i have dozens of dataset exactly same format with Date / Time column (Time stamp) and values. 

Hope to generate useful report comibining / calculation with datasets in same format, that is why i put date table for slicing...

Yes you mentioned, and i looked online most of relationship with date table is one to many, single direction. But it seems i should use different philosophy....do you have any idea?    

[Greg_Deckler · Accepted solution]

@ohjjang So does you Date table contain just dates or dates and timestamps? The issue with just Dates in your case is that these will not match up with dates other than timestamps of 00:00:00 (midnight).

[ohjjang]

@Greg_Deckler Thank you Sir, you are correct. I have just made normal Date table as easily getting information online, and this cause average values only time 00:00:00. So i have tried rebuild my date table which has hours and minuites than i could get matched value. Thanks Greg again!