Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Legend! Thanks for that.