Forum Discussion
TSGD2123
Helper I
3 years agoDAX 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...
- 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 )
TSGD2123
Helper I
3 years agoNot working, here is the desired output in case I didn't explain myself 😉
Today (13-1-2023)
- 1 day: A,B,C (inactive on 12-1)
- 2 days: A,C (inactive on 11-1 and 12-1)
- 3 days: A,C (inactive on 10-1, 11-1 and 12-1)
Thanks
johnt75
Super User
3 years agoBut B was also inactive on 12th, that's why that is being returned also.