Average of a value by date in one table between two dates in an unrelated table

[PaulHallam]

Hi,

I have two tables

Table 1 
description, datetime and value

Table 2
description, location, owner, startdatetime, enddatetime

The tables are linked on the description field.

I want to return a table visualisation in PowerBI desktop showing
description, location, owner, startdatetime, enddatetime, averagevalue

I cant get a measure to calculate the averagevalue between the startdatetime and enddatetime. Can anyone help please? (there are millions of rows of data)

Table 1

description	datetime	value
A	01/01/2022 09:00	1
A	01/01/2022 09:10	1
A	01/01/2022 09:20	1
A	01/01/2022 09:30	1
A	01/01/2022 09:40	1
A	01/01/2022 09:50	1
A	01/01/2022 10:00	1
A	02/01/2022 09:00	1
A	02/01/2022 09:00	0
A	02/01/2022 09:00	0
A	02/01/2022 09:00	0
A	02/01/2022 09:00	1
A	02/01/2022 09:00	1
A	02/01/2022 09:00	1
A	03/01/2022 09:00	1
A	03/01/2022 09:00	1
A	03/01/2022 09:00	1
A	03/01/2022 09:00	0
A	03/01/2022 09:00	0
A	03/01/2022 09:00	0
A	03/01/2022 09:00	1
A	04/01/2022 09:00	1
A	04/01/2022 09:00	1
A	04/01/2022 09:00	1
A	04/01/2022 09:00	1
A	04/01/2022 09:00	1
A	04/01/2022 09:00	1
A	04/01/2022 09:00	1
A	05/01/2022 09:00	1
A	05/01/2022 09:00	1
A	05/01/2022 09:00	1
A	05/01/2022 09:00	0
A	05/01/2022 09:00	0
A	05/01/2022 09:00	0
A	05/01/2022 09:00	0

Table 2

description	location	owner	startdatetime	enddatetime
A	Box1	John	01/01/2022 00:00	02/01/2022 22:00
A	Box2	John	02/01/2022 22:00	03/01/2022 22:00
A	Box2	Dave	03/01/2022 22:00	05/01/2022 22:00

Result wanted in table visualisation in PowerBi Desktop

description	location	owner	startdatetime	enddatetime	Average Value
A	Box1	John	01/01/2022 00:00	02/01/2022 22:00	78.6%
A	Box2	John	02/01/2022 22:00	03/01/2022 22:00	57.1%
A	Box2	Dave	03/01/2022 22:00	05/01/2022 22:00	71.4%

[v-easonf-msft]

Hi, @PaulHallam 

Try these formulas below:
Measue:

Count = 
VAR a =
    MAX ( 'Table 2'[startdatetime] )
VAR b =
    MAX ( 'Table 2'[enddatetime] )
RETURN
    CALCULATE (
        COUNT ( 'Table 1'[datetime] ),
        FILTER ( 'Table 1', 'Table 1'[datetime] >= a && 'Table 1'[datetime] < b )
    )

Total = 
VAR a =
    MAX ( 'Table 2'[startdatetime] )
VAR b =
    MAX ( 'Table 2'[enddatetime] )
RETURN
    CALCULATE (
        SUM ( 'Table 1'[value] ),
        FILTER ( 'Table 1', 'Table 1'[datetime] >= a && 'Table 1'[datetime] < b )
    )

Average Value = [Total]/[Count] 

Best Regards,
Community Support Team _ Eason

[PaulHallam]

Thanks 'v-easonf-msft'

PREVIEW

I have used the logic that you have provided but changed to one calculation as

Average =

VAR starttime = MAX(SensorInfo[SysStartTime])

VAR endtime = MAX(SensorInfo[SysEndTime])

RETURN

CALCULATE(

AVERAGE(Data[Value]),

FILTER(data,Data[DateTime]>=starttime && Data[DateTime]<endtime))

This works for the above query, thanks again BUT with totals on the totals dont return the count, total and average of the whole model it just returns the bottom row in the table. Is there a way to get the table totals working?

[v-easonf-msft · Accepted solution]

Hi, @PaulHallam 

Personally, I like to calculate the results step by step. Because once the result is wrong, I can easily find the problem.

I think you can add  the following measures to my original model to correct the total values.

Total1 = 
SUMX('Table 2','Table 2'[Total])

Count1 = SUMX('Table 2',[Count] )

Average1 = [Total1]/[Count1]

 Result：

Best Regards,
Community Support Team _ Eason

[PaulHallam]

v-easonf-msft

This works a treat for my demo data, thank you so much.

I need to add to the real report and hopefully there are no issues. If you get a minute can you explain the logic behind the Measures.

Thanks Again