topic Average of a value by date in one table between two dates in an unrelated table in DAX Commands and Tips

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

PaulHallam — Tue, 24 May 2022 10:54:25 GMT

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%

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

v-easonf-msft — Fri, 27 May 2022 10:06:37 GMT

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

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

PaulHallam — Fri, 27 May 2022 17:55:58 GMT

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?

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

v-easonf-msft — Wed, 01 Jun 2022 05:58:25 GMT

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

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

PaulHallam — Wed, 01 Jun 2022 09:56:18 GMT

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