Measure slow down the report or dont show

[ElinG]

Hi, I need help with this code to make it more efficient! Right now it doesn’t display any data because it’s too heavy.

Aktiva anställningar med aktiv uppmärkning =

CALCULATE(

    DISTINCTCOUNT('Anställda'[individual_partner_number]),

    FILTER(

        'Anställingsperiod',

        'Anställingsperiod'[Period_start_datum] <= EOMONTH(MAX('Kalender (innevarande minus 5 år)'[Date]), 0)

        && 'Anställingsperiod'[Period_slut_datum] >= DATE(

            YEAR(MIN('Kalender (innevarande minus 5 år)'[Date])),

            MONTH(MIN('Kalender (innevarande minus 5 år)'[Date])),

            1

        )

    ),

    FILTER(

        'analytics employment_fagroups',

        'analytics employment_fagroups'[**bleep**roup_valid_from_date] <= EOMONTH(MAX('Kalender (innevarande minus 5 år)'[Date]), 0)

        &&

        (

            'analytics employment_fagroups'[**bleep**roup_valid_to_date] >= DATE(

                YEAR(MIN('Kalender (innevarande minus 5 år)'[Date])),

                MONTH(MIN('Kalender (innevarande minus 5 år)'[Date])),

                1

            )

            || 'analytics employment_fagroups'[**bleep**roup_valid_to_date] = DATE(9999,12,31)

        )

    )

)

[Nabha-Ahmed]

Hi   

You can simplify and improve performance by pre-calculating your date limits and reducing the number of filters:

 

Aktiva anställningar med aktiv uppmärkning =
VAR _StartDate =
DATE(
YEAR(MIN('Kalender (innevarande minus 5 år)'[Date])),
MONTH(MIN('Kalender (innevarande minus 5 år)'[Date])),
1
)
VAR _EndDate =
EOMONTH(MAX('Kalender (innevarande minus 5 år)'[Date]), 0)
RETURN
CALCULATE(
DISTINCTCOUNT('Anställda'[individual_partner_number]),
KEEPFILTERS(
'Anställingsperiod'[Period_start_datum] <= _EndDate
&& 'Anställingsperiod'[Period_slut_datum] >= _StartDate
),
KEEPFILTERS(
'analytics employment_fagroups'[**bleep**roup_valid_from_date] <= _EndDate
&& (
'analytics employment_fagroups'[**bleep**roup_valid_to_date] >= _StartDate
|| 'analytics employment_fagroups'[**bleep**roup_valid_to_date] = DATE(9999,12,31)
)
)
)

Thanks✨🌹

put kudo 

@ElinG

[johnt75 · Accepted solution]

You can try

Aktiva anställningar med aktiv uppmärkning =
VAR StartDate =
    EOMONTH ( MAX ( 'Kalender (innevarande minus 5 år)'[Date] ), -1 ) + 1
VAR EndDate =
    EOMONTH ( MAX ( 'Kalender (innevarande minus 5 år)'[Date] ), 0 )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( 'Anställda'[individual_partner_number] ),
        'Anställingsperiod'[Period_start_datum] <= EndDate,
        'Anställingsperiod'[Period_slut_datum] >= StartDate,
        'analytics employment_fagroups'[**bleep**roup_valid_from_date] <= EndDate,
        'analytics employment_fagroups'[**bleep**roup_valid_to_date] >= StartDate
            || 'analytics employment_fagroups'[**bleep**roup_valid_to_date]
                = DATE ( 9999, 12, 31 )
    )
RETURN
    Result