Measure - Calculation from temporary table

[moosepng]

I have a measure that looks to calculate the number of clients whose status changes month to month.

Inactive last month and Active and Home this month = 
CALCULATE(
    COUNT(
        temp_table[Client_ID]),
        SampleTable[YearMonthIndex] = 202206 && SampleTable[Status_A] = "Active" && SampleTable[Status_B] = "Home" --- Active and home in June
)

However, to calculate this measure I first need to create a table (temp_table) that lists the Client_IDs that were Inactive last month.

temp_table = 
SUMMARIZE(
    FILTER(
        SampleTable, 
        SampleTable[YearMonthIndex] = 202205 && SampleTable[Status_A] = "Inactive"),
    SampleTable[Client_ID]
) 

Is there a way to include this temp_table into my measure?
I want to avoid creating multiple tables in my model.
My best guess was this:

Measure = 
var _TempTable =  
SUMMARIZE(
    FILTER(
        SampleTable, 
        SampleTable[YearMonthIndex] = 202205 && SampleTable[Status_A] = "Inactive"),
    SampleTable[Client_ID]
) 
RETURN
CALCULATE(
    COUNT(
        _TempTable, 
        SampleTable[YearMonthIndex] = 202206 && SampleTable[Status_A] = "Active" && SampleTable[Status_B] = "Home" --- Active and home in June
    )
)

Link to sample pbix file: https://drive.google.com/file/d/1JL8FwYhDazC93NqZQ_62PVjfxfAqg15z/view?usp=sharing

[v-jayw-msft · Accepted solution]

Hi @moosepng ,

Please check the formula.

Measure = 
var last_month_inactive = CALCULATETABLE(VALUES(SampleTable[Client_ID]),FILTER(ALLSELECTED(SampleTable),SampleTable[YearMonthIndex] = 202205 && SampleTable[Status_A] = "Inactive"))
return
CALCULATE(COUNT(SampleTable[Client_ID]),FILTER(ALLSELECTED(SampleTable),SampleTable[YearMonthIndex] = 202206 && SampleTable[Status_A] = "Active" && SampleTable[Status_B] = "Home" && SampleTable[Client_ID] in last_month_inactive))

To get the inactive IDs in last month and then count IDs that meet the conditions this month.

Best Regards,

Jay

[moosepng]

Legend! Thanks for that.

[amitchandak]

@moosepng , Create a measure like

M1=
CALCULATE(
COUNT(
temp_table[Client_ID]),
SampleTable[Status_A] = "Active" && SampleTable[Status_B] = "Home" --- Active and home in June
)

Create a date table with help from month year and use time intelligence. On in separate month year table have month Rank
Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense) //YYYYMM format

MTD Sales = CALCULATE([M1],DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE([M1]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Measure
This Month = CALCULATE([M1], FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE([M1], FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

[moosepng]

Is there a way to get M1 without first creating a new table (temp_table)?
For example: how would you find the number of clients who were 'Active' in May and 'Active' and 'Home' in June?