Aggregate hourly readings by Date

[jjr333]

I have a date table, 1 row per day.
And a data table with sensor readings per hour per day. The date column has values like 1-1-2013 23:00:00

The tables are joined, 1 to many.

Somehow I can't create a measure that calcualtes the SUM or AVERAGE per day.

O3 Sum = SUMX(LGResEscolar;'LGResEscolar'[O3])

It returns only 1 value for each date, i.s.o. a sum or average.

Any sugestions?

Cheers,

Julian

[PattemManohar · Accepted solution]

@jjr333 Please try to add an another column in your data table to derive the date from datetime value. Then join the date value field with Date Dimension, your measure will work fine !!

Measure is 

Test300 = SUMX(Test300GroupDateTime,Test300GroupDateTime[Value])

[jjr333]

@PattemManohar  Thank you!

[jjr333]

Hello Pattem,
What would be the DAX code using BETWEEN, or something similar (Date >= dimDate <= Date)?

Below an SQL example comparing week numbers after joining two table.

WHERE     
	AND HRS.IDHuis = @Huis 
	AND HRS.Jaar = @Jaar 
	AND HRS.WEEK BETWEEN @WeekBegin AND @WeekEind