Average for mesure with date slicer

[sigtopo]

I have a dataset with the following:

• id_sensor: sensor identifier
• measurement_date: measurement date
• data_value: value

I want to represent average values over a date range that the Powerbi dashboard user can select with a slicer.
I am in directQuery mode on a Postgresql database.
Is it possible ?

Here is a sample of data

id_sensor	measurement_date	data_value
12024	02/06/2023 17:21	0.5
12024	22/05/2023 17:02	0.25
12024	23/05/2023 10:25	0.25
12024	01/06/2023 16:58	0.5
12024	15/05/2023 06:44	0
12024	15/05/2023 17:04	0.5
12024	16/05/2023 07:11	0.25
12024	17/05/2023 07:16	0.25
12024	19/05/2023 07:42	0
12024	19/05/2023 17:18	1.25
12024	20/05/2023 07:19	1
12024	10/06/2023 16:26	0.5
12024	11/06/2023 06:56	1.25
12024	12/06/2023 14:50	1.25
12946	22/05/2023 18:29	0.25
12946	23/05/2023 06:19	0.25
12946	03/06/2023 14:52	0.5
12946	24/05/2023 13:49	0.25
12946	05/06/2023 14:03	0.5
12946	25/05/2023 15:52	0.5
12946	06/06/2023 14:09	0.75
12946	27/05/2023 15:08	1

....

[sigtopo]

It's perfect ! Thanks !

[v-zhangti · Accepted solution]

Hi, @sigtopo 

You can try the following methods. This formula can be run in Direct query mode.

Measure =
AVERAGEX ( FILTER ( 'Table',
        [measurement_date] >= MIN ( 'Table'[measurement_date] )
            && [measurement_date] <= MAX ( 'Table'[measurement_date] )
            && [id_sensor] = SELECTEDVALUE ( 'Table'[id_sensor] )
    ),
    [data_value]
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.