Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a KPI based on a filter

I'm hoping this is very simple, and has probably been answered but I don't know what to search for to find...

 

I want to create a kpi to show a filtered value. For example, I have a table like so, and I want a KPI to show 19 - the new accounts added in February.

 

I have created a measure that evaluates to the last complete billing period of 202002 (202003 is current and not complete)

LastCompleteBillingPeriod = max(Accounts[BillingPeriod])-1
 
I just don't know how to write the measure that will return 19 as the KPI value based on the LastCompleteBillingPeriod measure
 
 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Something along the lines of the following. I am not sure if NewAccounts is a measure or not, I'm going to guess that it is:

     

    New Measure =
  VAR __item = [Your last complete billing measure goes here]
RETURN
  MAXX(
    FILTER(
      SUMMARIZE('Table',[BillingPeriod],"__NewAccounts",[NewAccounts]),
      [BillingPeriod = __item
    ),
    [__NewAccounts]
  )

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Something along the lines of the following. I am not sure if NewAccounts is a measure or not, I'm going to guess that it is:

     

    New Measure =
  VAR __item = [Your last complete billing measure goes here]
RETURN
  MAXX(
    FILTER(
      SUMMARIZE('Table',[BillingPeriod],"__NewAccounts",[NewAccounts]),
      [BillingPeriod = __item
    ),
    [__NewAccounts]
  )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks very much Greg, that worked...now I just have to break it down so I understand it!

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    You could try below measure to see whether it work or not

    MEASURE 3 = VAR MAXDAY=MAXX(FILTER(T2,T2[date]<YEAR(TODAY())*100+MONTH(TODAY())),T2[date]) RETURN CALCULATE(SUM(T2[amount]), FILTER(T2, T2[date]=MAXDAY))

    or 

    Measure 2 = CALCULATE(SUM(T2[amount]), FILTER(T2, T2[date]=YEAR(TODAY())*100+MONTH(TODAY())-1))

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.