Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
Legend! Thanks for that.
@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?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
101 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
99 | |
83 | |
63 | |
54 |