Forum Discussion

Icon for Helper I rank

Helper I

6 years ago

Solved

Present the last month data

Hello,

I have Months slicer.

I have a table with Emp.Id, Accumulated sickness dates and their ranges:

Emp.Id	Months	Accumulates Sickness days	Sickness ranges
100	Jan	0%	0%-1.5%
100	Feb	1.4%	0%-1.5%
100	Mar	2.5%	1.5-3%
100	April	1.8%	1.5-3%
100	May	1.1%	0%-1.5%

etc.

I would like to present a visual table/chart with the last month Accumulates sickness days.

E.g If I choose in slicer January-March I would like to present 2.5%.

If I choose in slicer January-April I would like to present 1.8%.

How can I do it?

I suggest you to add a date column

Measure = 
VAR d=max('Table'[date])
Return maxx(FILTER('Table','Table'[date]=d),'Table'[accumulates])

ryan_mayu
6 years ago
gilr1975

I am not sure how your data looks like. You still want the number of max month? Are there duplicated employees in the table for each month?

I created test data.
Measure = VAR d=max('Table'[date]) Return calculate(DISTINCTCOUNT('Table'[employee]),FILTER('Table','Table'[date]=d))

It will be better if you share the excel screenshot of your data structure and expected results.

5 Replies

ryan_mayu
Super User
6 years ago
gilr1975

I suggest you to add a date column
Measure = VAR d=max('Table'[date]) Return maxx(FILTER('Table','Table'[date]=d),'Table'[accumulates])
- gilr1975
  Helper I
  6 years ago
  ryan_mayu
  Thank you so much!
  One more question:
  I also need to present the number of employees per ranges.
  I have a range table with their min and max, but it counts all employess for all months and I need only number of employees for the last selected month.
  How can I do it?
  - ryan_mayu
    Super User
    6 years ago
    gilr1975
    
    I am not sure how your data looks like. You still want the number of max month? Are there duplicated employees in the table for each month?
    
    I created test data.
    Measure = VAR d=max('Table'[date]) Return calculate(DISTINCTCOUNT('Table'[employee]),FILTER('Table','Table'[date]=d))
    
    It will be better if you share the excel screenshot of your data structure and expected results.
Tahreem24
Super User
6 years ago
gilr1975 ,

You can use the below Measure:

MAX = CALUCLATE(MAX(TableNAme[Accumulates]),FILTER(TableName,MAX(TableName[Months])))

Related content