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[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

 

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

4 Replies

  • 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?

  • 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.