Forum Discussion

Chennaiah's avatar
Chennaiah
Frequent Visitor
8 years ago
Solved

Last 5 Years running total

  I have below data , want show running headcount for last 5 years.   Year Headcount 2010 100 2011 200 2012 300 2013 400 2014 500 2015 600 2016 700 2017 800 ...
Dashboards
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi Chennaiah,

     

    You can create a measure below: 

     

    Running Headcount = var LY=MAXX(ALL(Table1),'Table1'[Year])
    return
    IF(MAX(Table1[Year])<=LY-5,BLANK(),CALCULATE(SUM(Table1[Headcount]),FILTER(ALL(Table1),Table1[Year]<=MAX(Table1[Year]))))

     

     

     

    Best Regards,
    Qiuyun Yu 

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Chennaiah,

 

You can create a measure below: 

 

Running Headcount = var LY=MAXX(ALL(Table1),'Table1'[Year])
return
IF(MAX(Table1[Year])<=LY-5,BLANK(),CALCULATE(SUM(Table1[Headcount]),FILTER(ALL(Table1),Table1[Year]<=MAX(Table1[Year]))))

 

 

 

Best Regards,
Qiuyun Yu