Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
moosepng
Helper II
Helper II

Measure - Calculation from temporary table

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

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Legend! Thanks for that.

amitchandak
Super User
Super User

@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))

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?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.