DAX flag and dynamic interval measure

[TSGD2123]

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	12/01/2023	0
C	10/01/2023	0
C	11/01/2023	0
C	12/01/2023	0

I'm asked to create a visual table displaying the inactive enterprises for the past x days:

Lets say today is 12/1/23 and for the past 3 days (10, 11, and 12) would be:

Enterprise

A

C

The enterprises not active on days 11 and 10. 

I need:

1) Create a measure or anything to show that output

2) Make the time period dynamic :

Enterprise

A

B

C

Thanks a lot in advance

[johnt75]

You'd need to set up a numeric range parameter called Num days then you could create a measure like

Enterprise is visible =
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] )
    )
RETURN
    IF ( CurrentEnterprise IN InactiveEnterprises, 1 )

and this is a visual level filter to a table visual set to only show when the value is 1

[FreemanZ]

hi @johnt75 

Could you explain how to use this measure?

[johnt75]

Put the Enterprise column into a table or matrix and then add the measure as a visual level filter, to only show when the value is 1

[TSGD2123]

Unfortunately I cannot enclose the pbix but here here the screenshots:

 

As can be seen, if I select any day, all 3 enterprises have measure value 1, if none are selected, no enterprise shows up.

[johnt75]

All 3 enterprises have an inactive entry for 12th, that's why they all show up whenever you select a day

[TSGD2123]

The flag=1 is active:

- Day 1 selected: date: 12th: Enterprises A, B, C are inactive. Desired output: A, B, C

- Day 2 selected: dates: 11th, 12th: Enterprises A, C are inactive on both 11th or 12th. Desired output: A, C

- Day 3 selected: dates: 10th,11th, 12th: Enterprises A, C are inactive on 10th,11th and 12th, B has been active on the 3 day period so: Desired output: A, C

Basically you select the amount of days you go back from today, thats your period, I need the enterprises that have been inactive (flag=0) through the period to show up, if there's one single day a company has flag = 1, it doesn't have to appear.

Sorry I wasn`t clear previously 

[johnt75 · Accepted solution]

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]

One last question @johnt75 please, is it possible to create anothe measure to show the distinctcount of Enterprises not active for the current selected period? (3 when day 1; 2 when day 2...) 

Thanks!!

[johnt75]

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

[TSGD2123]

Works wonderfully, thanks a lot for your time and patience @johnt75 

[TSGD2123]

Not 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]

But B was also inactive on 12th, that's why that is being returned also.