Measure that sums only the data from last of selected years

[Draszor]

Dear All, 

I regarded my problem as very bsic, but well - I got stuck. 

I have a data table showing headcount turnover from 1996-2020. it is very simple - if we employ someone, then we add another record to this table with all data and +1 as the HC increase. If someone leaves then we add a new record with all basic data and -1. 

So, summing up HeadCount column, I get my actual HC. 

I am using the slicer for years, where I can choose the time span (assume I choose 2005-2015). Now, I would like to build a measure that would sum up my HEADCOUNT from the oldest date until the chosen highers chosen year (in my example, up until 2015). that way, changing the slicer I would see my HeadCount number for the highest of chosen years. how to do this?

I am sure it must be something around: Calculate(sum(HeadCount);Filter(all(table name),date <= max(Date)) but this works fine for showing line chart with dates on x array, however does not work at all for showing a pie chart with last of chosen years Headcoubnt per department. 

[nandukrishnavs]

@Draszor 

If you are using ALL(), specify the date column only, not the entire table.

[Draszor]

thank you very much - it works. Now I know that I can also use the column (with ALL) in the Fiter function.

Therefore, my formula to get the HC i the last year selected is:

calculate(

sum(HC_DATA[HC]);

filter(all(HC_DATA[Year]);

HC_DATA[Year]<-max(HC_DATA[Year]

I have a division of pie charts by department, as I wanted.

Now, I'm clicking on part of the pie chart that represents the production department. I expected my total run line chart (showing the total running HC for the selected years, but calculated from the oldest date) would show the execution total for the selected department, but it does not. It shows very shattered figures in fact. what's wrong with my total execution formula below:

HC running Total - calculate(

sum(HC_DATA[HC]);

filter(ALL(HC_DATA);

HC_DATA[DateofHireLeave]<-max(HC_DATA[DateofHireLeave]

)))

thank you very much in advance for your track

[Draszor]

thanks a lot - it works. Now I know that I can use as well column (with ALL) in Fiter function. 

So, my formula for getting the HC i last selected year is:

calculate(

sum(HC_DATA[HC]);

filter(all(HC_DATA[Year]);

HC_DATA[Year]<=max(HC_DATA[Year]

I get a beaultiful pie chart split per departments, as I wanted. 

Now, I am clicking on part of the pie chart that represents the production department. I was expecting that my running total line chart (showing running total of HC for selected years, but calculated from the oldest date) would show running total for selected department, but it does not. It shows very weired figures in fact. what is wrong in my running total formula below:

HC running Total = calculate(

sum(HC_DATA[HC]);

filter(ALL(HC_DATA);

HC_DATA[DateofHireLeave]<=max(HC_DATA[DateofHireLeave]

)))

thanks a lot in advance for your hint

[v-yingjl · Accepted solution]

Hi @Draszor ,

If you want to show running total of HC for selected years, you can use all() like this not all the table:

HC running Total = 
CALCULATE (
    SUM ( HC_DATA[HC] ),
    FILTER (
        ALL ( HC_DATA[DateofHireLeave]),
        HC_DATA[DateofHireLeave] <= MAX ( HC_DATA[DateofHireLeave] )
    )
)

Maybe it will be your expected output:

In addition, you can use the ALL and ALLEXCEPT functions in different scenarios:

Best Regards,
Yingjie Li

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

[Draszor]

thanks a lot. ALLEXCEPT did its work. 

using ALL with column name, unfortunatelly does not work - running total is not running total any more. 

BR

Draszor