Return max from rows

[rsanyoto]

Hello guys,

I am new with DAX and Power BI so maybe you could help me with this use case.

I have a table with employee id and Month.

I would like to calculate the count of employeeID based on the latest/max from the column Month.

EmployeeID	Month
1	01
2	02
3	03
4	04
5	05
6	06
7	07
8	07

After executing the right measure the result would be :

Month	New Measure
01	1
02	1
03	1
04	1
05	1
06	1
07	2

Any suggestions?

Kind regards,

Rega

[dobregon · Accepted solution]

@rsanyoto 

So, do you want to have a unique value that give to you the count of employees in the last month that you have in the databse automatically, right?

To do that, you can do 2 measures

Max month = max(month) --> This give to you the max number of month in your table (maybe you need to combine with year if you want to analyse different years). in that case you need to create a column Year-Month like (2020-01) and then the max like Max period = max(year-month)

Then you can do a calculate measure to count by last period

calculate(count(employee), filter(table, year-month column = measure max period))

[amitchandak]

@rsanyoto , You are trying to filter the data for month 12, there is no data for month 12, someother logic is rquired.

Can you please elaborate the problem you are facing

[rsanyoto]

Hi guys,

What if my month column from 1(january) -12 (december)?

I was thinking like:

CALCULATE([Total Employee],'TableEmpoyee'[Month] = "12")

[AllisonKennedy]

@rsanyoto  You can just create a measure: 

Total Employees = COUNT(Table[EmployeeID])

Then create a table visualization. Put Table[Month] and [Total Employees] in the visual. 

[amitchandak]

@rsanyoto , with this data it should be

count(Table[EmployeeID])

or

distinctcount(Table[EmployeeID])