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.

 

YearHeadcount
2010100
2011200
2012300
2013400
2014500
2015600
2016700
2017800
2018900

 

Output :

YearRunning Headcount
2010 
2011 
2012 
2013 
20141500
20152100
20162800
20173600
20184500
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 

2 Replies

  • 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