measure average active in period

[Margreet]

[Margreet]

[Margreet]

[Jihwan_Kim]

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 ) )

[Margreet]

[Margreet]

[Jihwan_Kim · Accepted solution]

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.

[Margreet]

[Jihwan_Kim]

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 ) )