Maximum Year and Month

[sh_12345]

Dear Experts,

Hope you are fine,

I am unable to find the Dax solution, displaying only maximum value of year and month wise of employees from the following table:

Month	2020	2021	2022
Jan		18200	20000
Feb		18200	20000
Mar		18200	20000
Apr		18200	20000
May		18200	20000
Jun		18200	20000
Jul		18200	25000
Aug		18200	25000
Sep		20000	25000
Oct		20000	25000
Nov		20000	25000
Dec	18200	20000

Note: Calendar table is made separately.

Please advise any solution

[v-zhangti]

Hi, @sh_12345 

You can try the following methods.

Measure = 
Var _maxvalue=CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Date','Date'[Year]))
Var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Value]=_maxvalue))
Return
IF(SELECTEDVALUE('Date'[Month])=MONTH(_mindate),_maxvalue,0)

Is this the result you expect? If not, please provide more information.

Best Regards,

Community Support Team _Charlotte

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

[sh_12345]

Dear @v-zhangti,

Thank you i wanted the same result what you provided but i want the employee name in the slicer.

Please advice the same with employee name in the slicer as well

[v-zhangti]

Hi, @sh_12345 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. 

Best Regards,

Community Support Team _Charlotte

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

[sh_12345]

Following tables have been made accordingly in my data as well.

1) Employee Name Column has been added in the your PBI Sheet

 2) Calendar table should be made as displayed follows:

Execpeted result is what you have mentioned lately, employee name is to be included in the slicer

[v-zhangti]

Hi, @sh_12345 

Measure = 
Var _maxvalue=CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Date','Date'[Year]))
Var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Value]=_maxvalue))
Var _N1=IF(MAX('Date'[Month])=MONTH(_mindate),_maxvalue,0)
Return
IF(SELECTEDVALUE('Table'[Employee Name])<>BLANK(),SUM('Table'[Value]),_N1)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[sh_12345]

Please resend the working using with my Calendar Table DAX calculation that was sent you in the last Msg.

[v-zhangti]

Hi, @sh_12345 

You can try the following methods.

Measure = 
Var _maxvalue=CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Calender','Calender'[Year]))
Var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Value]=_maxvalue))
Var _N1=IF(MAX('Calender'[Month No])=MONTH(_mindate),_maxvalue,0)
Return
IF(SELECTEDVALUE('Table'[Employee Name])<>BLANK(),SUM('Table'[Value]),_N1)

Please check the attachment.

Best Regards,

Community Support Team _Charlotte

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

[sh_12345]

The expected result is still incorrect.

Only the highest value in the year should be displayed at once in the month in the aforementioned table

[sh_12345]

@v-zhangti 

[v-zhangti]

Hi, @sh_12345 

Can you tell the names of the employees in the slicer and how the sample data you provided are connected?

Best Regards,

Community Support Team _Charlotte

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

[sh_12345]

i want the maximum value in the above table in the year and Month. Provided that minimum value will be replaced to 0 or blank. 

[DimaMD]

hi @sh_12345 , Can you provide your expected result?

[sh_12345]

Expected result will be the maximum value of the year

Dec 2020 - 18,200

Sep 2021 - 20,000

Jul 2022- 25,000

[sh_12345]

But i want only the maximum value should be displayed in the year in aforementioned table. 

[DimaMD]

@sh_12345  review the file and tell me if this is your expected result

[sh_12345]

The table should be displayed all the months and years. The minimum value should be mentioned "0" or blank. Only the maximum value should be displayed. 

[sh_12345]

Employee names in the slicer

[sh_12345]