Forum Discussion

TSGD2123's avatar
TSGD2123
Icon for Helper I rankHelper I
3 years ago
Solved

DAX flag and dynamic interval measure

Hi all,   I have some data like: Enterprise Date Flag active A 09/01/2023  1 A 10/01/2023  0 A 11/01/2023  0 A 12/01/2023  0 B 10/01/2023  0 B 11/01/2023  1 B...
  • johnt75's avatar
    johnt75
    3 years ago

    Right, you want to exclude enterprises which have an active entry during the period. You can use

    VAR CurrentEnterprise = SELECTEDVALUE( 'Table'[Enterprise] )
    VAR NumDays = SELECTEDVALUE( 'Num days'[Num days] )
    VAR StartDate = TODAY( ) - NumDays
    VAR ChosenDates = CALENDAR( StartDate, TODAY( ) )
    VAR InactiveEnterprises =
    	CALCULATETABLE(
    		VALUES( 'Table'[Enterprise] ),
    		TREATAS( ChosenDates, 'Table'[Date] ),
    		TREATAS( { 0 }, 'Table'[Flag active] )
    	)
    VAR ActiveEnterprises =
    	CALCULATETABLE(
    		VALUES( 'Table'[Enterprise] ),
    		TREATAS( ChosenDates, 'Table'[Date] ),
    		TREATAS( { 1 }, 'Table'[Flag active] )
    	)
    RETURN
    	IF(
    		CurrentEnterprise IN
    			EXCEPT( InactiveEnterprises, ActiveEnterprises ),
    		1
    	)