SUM column based on most recent value in another column

[Haleem]

Hi, 

I am pretty new to DAX and power BI. Can you help me with below question. 

I would like to create a measure where I can SUM column 'Headcount' based on two filters, 1) Fiscal year (which is simple) and 2) most recently added 'Period'. 

Current Headount 2018 =  SUM of [headcount] where fiscal year = 2018 and Period = most  recently added period.... currently it should reflect P03, but next month it should reflect P04 and so on.... please find my sample  data below. 

Pleasea note, since it's headcount so I cannot simple add P1+P2+P3 to get the current level of headcounts. Current level of Headcount is always equal to the most recently added period...

Period	Fiscal Year	Headcount
P01	FY16	10
P02	FY16	12
P03	FY16	15
P01	FY17	15
P02	FY17	16
P03	FY17	18
P01	FY18	18
P02	FY18	22
P03	FY18	25

[v-lili6-msft]

Hi@ Haleem

I have tested on my side, but not reproduce the issue. and we need more detail steps for us.

Please share your pbix for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Haleem]

Hi again, 

Sorry for replying too late. I have now uploaded files to Dropbox. here is the https://www.dropbox.com/sh/nb3gob9v5rmbq1z/AAAy8PBxZAcVEAfsBdgvCnYFa?dl=0

Problem: As shown in the screen shot, I can not get FY19  P2 data in the chart, because the max period in the measure is P12 and we do not have any actuals for P12 in FY19. So far we only got actuals for P2 in FY19, but of course in the month column we have P1 to P12 because we have another column showing FTE estimates where we have FTEs from P1 - P12. 

. FTE Actuals column is updated with actuals FTE every month. Until now we have closed P1 and P2, so FTE actual column contains data for  P1 and P2. There is no data in FTE actual column for P3 - P12 because it will only be populated when we close P3 and so on. 

[v-lili6-msft]

Hi@ Haleem

After my research, you can do these follow my steps like below:

Step 1:

Add two column

Year = RIGHT(Table3[Fiscal Year],2)
Per = RIGHT(Table3[Period],2)

Step 2:

Change these two new column data type to Whole Number

Step 3:

Add the measure:

Current Headount =
VAR maxyear =
    MAX ( Table3[Year] )
RETURN
    VAR maxper =
        CALCULATE ( MAX ( Table3[Per] ), FILTER ( Table3, Table3[Year] = maxyear ) )
    RETURN
        CALCULATE (
            VALUES ( Table3[Headcount] ),
            FILTER ( Table3, Table3[Year] = maxyear && Table3[Per] = maxper )
        )

Result:

Best Regards,

Lin

[Haleem]

Thanks a lot for your quick and specific response. I have applied the measure exactly the same way as you mentioned apart that I use SUM function instead of VALUES because in my real data I have other columns as well e.g. cost centers and employee group etc.

The only problem I encountered is that my latest year (FY18) is missing when I show the headcount data Year on Year - shown in 1st screenshot, but when I "Expand" to the next level where I have Quarter on Quarter split, then FY18 data is being shown as you can see in the 2nd screenshot.