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

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , 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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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?