topic Re: measure average active in period in DAX Commands and Tips

measure average active in period

Margreet — Sat, 15 Jul 2023 09:09:21 GMT

Re: measure average active in period

Jihwan_Kim — Sat, 15 Jul 2023 12:51:31 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

expected result measure: = VAR _t = ADDCOLUMNS ( FILTER ( Data, Data[StartDate] <= MAX ( 'Calendar'[Date] ) && Data[EndDate] >= MIN ( 'Calendar'[Date] ) ), "@daycount", SWITCH ( TRUE (), Data[EndDate] > MAX ( 'Calendar'[Date] ) && Data[StartDate] < MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) - MIN ( 'Calendar'[Date] ) + 1, Data[EndDate] > MAX ( 'Calendar'[Date] ) && Data[StartDate] >= MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) - Data[StartDate] + 1, Data[EndDate] <= MAX ( 'Calendar'[Date] ) && Data[StartDate] < MIN ( 'Calendar'[Date] ), Data[EndDate] - MIN ( 'Calendar'[Date] ) + 1, Data[EndDate] <= MAX ( 'Calendar'[Date] ) && Data[StartDate] >= MIN ( 'Calendar'[Date] ), Data[EndDate] - Data[StartDate] + 1 ) ) RETURN DIVIDE ( SUMX ( _t, [@daycount] ), COUNTROWS ( _t ) )

Re: measure average active in period

Margreet — Sat, 15 Jul 2023 17:42:37 GMT

Re: measure average active in period

Jihwan_Kim — Sun, 16 Jul 2023 07:32:24 GMT

Hi,

Thank you for your message.

expected result measure: = VAR _t = ADDCOLUMNS ( FILTER ( ALL ( Data ), Data[Category] = MAX ( Category[Category] ) && Data[StartDate] <= MAX ( 'Calendar'[Date] ) && Data[EndDate] >= MIN ( 'Calendar'[Date] ) ), "@daycount", SWITCH ( TRUE (), Data[EndDate] > MAX ( 'Calendar'[Date] ) && Data[StartDate] < MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) - MIN ( 'Calendar'[Date] ) + 1, Data[EndDate] > MAX ( 'Calendar'[Date] ) && Data[StartDate] >= MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) - Data[StartDate] + 1, Data[EndDate] <= MAX ( 'Calendar'[Date] ) && Data[StartDate] < MIN ( 'Calendar'[Date] ), Data[EndDate] - MIN ( 'Calendar'[Date] ) + 1, Data[EndDate] <= MAX ( 'Calendar'[Date] ) && Data[StartDate] >= MIN ( 'Calendar'[Date] ), Data[EndDate] - Data[StartDate] + 1 ) ) RETURN DIVIDE ( SUMX ( _t, [@daycount] ), COUNTROWS ( _t ) )

Re: measure average active in period

Margreet — Sun, 16 Jul 2023 09:02:25 GMT

Re: measure average active in period

Margreet — Sun, 16 Jul 2023 10:34:00 GMT

Re: measure average active in period

Margreet — Tue, 18 Jul 2023 18:28:16 GMT

Re: measure average active in period

Jihwan_Kim — Wed, 19 Jul 2023 03:05:55 GMT

Hi,

Please share your sample pbix file's link, and then I can see what other columns are also involved. In the picture, I could not see what is under Category column in the visualization.

Thanks.

Re: measure average active in period

Margreet — Wed, 19 Jul 2023 06:31:39 GMT