0 Replies Latest reply: Apr 16, 2016 9:32 AM by Siddharth Sheshadri RSS

    Help with formula for prev. month & this month KPI

    Siddharth Sheshadri

      Okay, I need  a huge favor. Not sure who will be able to crack this for me. I sure will owe them a favor

      I am calculating a KPI called Busyness below is the formula. All of them used are pretty much dimensions.

       

      SUM(IF([Busyness.Binary personal time]=0,[Busyness.Billable Hours]))/

      SUM([Busyness Lookup]/[Num_of_TS_Entries_perday.No. Of Entries])

       

      Please see the attached graph. It is a combo chart but currently has only one measure which is Busyness for 3 years i.e. complete metadata.

      I need 2 other measures which calculates the above KPIs for prev month and this month. So the visual should have 1 line showing complete data, another showing only last month & another showing only this month so we can easily compare even without filtering.

       

      If it helps, I have 2 dimensions which say if the row is this month or not. Just unsure as to how to go about it.

      I tried the below formula but it didn't work, newbie

       

      IF([Binary Last Month or Not]=1,SUM(IF([Busyness.Binary personal time]=0,[Busyness.Billable Hours]))/

      SUM([Busyness Lookup]/[Num_of_TS_Entries_perday.No. Of Entries]),0)