Comparing fact table value to an earlier value in same table

[colinhahn]

I am trying to create a measure that will show how many members our group has gained or lost since our last month.

The data source is a spreadsheet exported from our membership software once a month, with rows for member ID, member status, and a couple of other columns.

I have transformed that data so that each row also contains the date of the report. So, my fact table looks something like this:

10/31/2021 | MemberA_ID | Active
10/31/2021 | MemberB_ID | Active
11/30/2021 | MemberA_ID | Inactive
11/30/2021 | MemberB_ID | Active
11/30/2021 | MemberC_ID | Active

The end visual will be a line graph with the report dates on the X axis and the Y axis showing two measures:
a) the count of how many members are active at the given report date AND were not active at the most previous report date (i.e., gained members)
b) the count of how many members are inactive at the given report date AND were active at the most previous report date (i.e., lost members)

I am struggling with how to create this measure. I think I need to do some manipulation of the filter contexts because the report date is filtered based on the X axis value and I need to look at the previous report date at that same value.

How can I do this?

 

[Jeffrey Mahoney]

There might be some people that can come up with a more elegant solution.

Book1
	A	B	C	D	E	F	G	H	I
1				Changes				Changes
2	Date	Member ID	Status	Change to Inactive	Change to Active			Inactive	Active
3	10/31/2021	MemberA_ID	Active	0	1		10/31/2021	0	2
4	10/31/2021	MemberB_ID	Active	0	1		11/30/2021	1	1
5	11/30/2021	MemberA_ID	Inactive	1	0		12/31/2021	0	2
6	11/30/2021	MemberB_ID	Active	0	0
7	11/30/2021	MemberC_ID	Active	0	1
8	12/31/2021	MemberA_ID	Active	0	1
9	12/31/2021	MemberB_ID	Active	0	0
10	12/31/2021	MemberC_ID	Active	0	0
11	12/31/2021	MemberD_ID	Active	0	1
Doug123

Cell Formulas
Range	Formula
D3:D11	D3	=IF(C3="Inactive",COUNTIFS($C:$C,"Active",$A:$A,MAXIFS($A:$A,$A:$A,"<"&$A3),$B:$B,$B3),0)
E3:E11	E3	=IF(AND(C3="Active",COUNTIFS($B:$B,$B3,$A:$A,"<"&A3)=0),1,COUNTIFS($C:$C,"Inactive",$A:$A,MAXIFS($A:$A,$A:$A,"<"&$A3),$B:$B,$B3))
G3:G5	G3	=SORT(UNIQUE(A3:A11))
H3:H5	H3	=SUMIFS($D:$D,$A:$A,G3)
I3:I5	I3	=SUMIFS($E:$E,$A:$A,G3)
Dynamic array formulas.

 

[colinhahn]

I'm working in Power BI for this, not Excel.

 

[macfuller]

Assuming:
a) Each member ID only appears once per month
b) you are only interested in consecutive month variance
c) Inactive members were still on your rolls in prior months so we can look for an "inactive" status. A new member wouldn't have been on the rolls so we'd have to test if their member ID was extant in prior months if you want to count them as a switch in status. That's a different set of logic then.

I think you'll have to use a new column in your data since we want to match against the member ID - that's tougher in a measure. Your table will have a filter on the month you're interested in.

(you didn't provide the table or field names so substitute as you wish). Try something along these lines...

New column...
[Active from Prior Month] =
VAR member_id = TableName[Member ID]
VAR member_status = "Active"
VAR last_month =
DATEADD ( TableName[Date], -1, MONTH )
VAR inactive_last_month =
CALCULATE (
COUNTROWS ( TableName ),
TableName[Member ID] = member_id,
MONTH ( TableName[Date] ) = MONTH ( last_month )
&& YEAR ( TableName[Date] ) = YEAR ( last_month ),
TableName[MemberStatus] <> member_status
)
VAR became_active =
IF ( inactive_last_month > 0, "Became Active", BLANK () )
RETURN
became_active

New measure...
[Newly Active]:=CALCULATE ( COUNTROWS(TableName), TableName[Active from Prior Month] = "Became Active")

And reverse the logic for the members who became inactive.