Show the value in a specific Month based on Slicer filter

[Anno2019]

Hi Guru's

I hope this is a simple one.

I will have a slicer filter as the image below indicates.  In this example my slicer represents Jan to April which the user can adjust.

I have a field for # of Employees, this is not a field that is "summed" as the # needs to based on the oldest month details, in the below example, I will need the # of employees as of April, so basically April's total # of employees.

I don't want my calculation to sum from Jan to April.  Not sure how to build this formula.  Total for April would be 24.  But if the User where to choose the period Jan to Jun "1 - 6" then the result in my formula should show 23.

[Asac_14 · Accepted solution]

Hi @Anno2019 ,

You have missed a bracket in the measure after the sum that's why you were getting error. Please use the below updated error. Do mark this as your solution if it solves your problem.

Employee Total Asac_14 Answer =

Var Month_Max=Max('DATA Testing'[Month Num])

Var Total_Value=Calculate(Sum('DATA Testing'[Amount]),'DATA Testing'[Month Num]=Month_Max)

Return Total_Value

[v-diye-msft]

Hi @Anno2019 

Please use below masure

Measure 6 = var a = MAX('Table (5)'[Monthnum])
Return
CALCULATE(MAX('Table (5)'[#of employee]),FILTER(ALL('Table (5)'),[Monthnum]=a))

[Anno2019]

@v-diye-msft  and @Asac_14 

Thank you both for the replies.  I tried both the answers but I am not doing something correctly.

I put a pbix file together which better reflects my current report.  I also created two measures using each example.

not sure if it is because my [Number of Employees] is actually a measure.

https://www.dropbox.com/s/qd4yghgw6bwnees/Show%20value%20of%20a%20specific%20month%20based%20on%20sl... 

[v-diye-msft · Accepted solution]

Hi @Anno2019 

Take a try of this one:

Measure = var a = MAX('DATA Testing'[MonthNum])
Return
MAXX(FILTER('DATA Testing',[MonthNum]=a),[Number of Employees])

 

[Asac_14 · Accepted solution]

Hi @Anno2019 ,

You have missed a bracket in the measure after the sum that's why you were getting error. Please use the below updated error. Do mark this as your solution if it solves your problem.

Employee Total Asac_14 Answer =

Var Month_Max=Max('DATA Testing'[Month Num])

Var Total_Value=Calculate(Sum('DATA Testing'[Amount]),'DATA Testing'[Month Num]=Month_Max)

Return Total_Value

[Asac_14]

Hi @Anno2019 ,

You can use the below measure 

Employee Total = 

Var Month_Max=Max('Table'[Month Num])

Var Total_Value=Calculate(Sum('Table'[Number of Employees],Table'[Month Num]=Month_Max)

Return Total_Value

Please mark this as solution and give a kudos if the above measue works for you.

PS I created and tested it in my local it does works 😉