topic Re: Maximum Year and Month in DAX Commands and Tips

Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 11:32:41 GMT

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

Re: Maximum Year and Month

DimaMD — Tue, 03 Jan 2023 12:32:38 GMT

hi sh_12345 , Can you provide your expected result?

Re: Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 14:03:24 GMT

Expected result will be the maximum value of the year

Dec 2020 - 18,200

Sep 2021 - 20,000

Jul 2022- 25,000

Re: Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 14:14:17 GMT

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

Re: Maximum Year and Month

DimaMD — Tue, 03 Jan 2023 14:40:58 GMT

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

Re: Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 15:19:27 GMT

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.

Re: Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 16:48:21 GMT

Employee names in the slicer

Re: Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 18:11:06 GMT

Re: Maximum Year and Month

sh_12345 — Tue, 03 Jan 2023 18:13:43 GMT

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.

Re: Maximum Year and Month

v-zhangti — Wed, 04 Jan 2023 06:18:40 GMT

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.

Re: Maximum Year and Month

sh_12345 — Wed, 04 Jan 2023 11:33:23 GMT

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

Re: Maximum Year and Month

v-zhangti — Fri, 06 Jan 2023 07:18:22 GMT

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.

Re: Maximum Year and Month

v-zhangti — Mon, 09 Jan 2023 06:31:22 GMT

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.

Re: Maximum Year and Month

sh_12345 — Mon, 09 Jan 2023 07:23:31 GMT

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

Re: Maximum Year and Month

v-zhangti — Mon, 09 Jan 2023 07:56:11 GMT

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.

Re: Maximum Year and Month

sh_12345 — Mon, 09 Jan 2023 09:18:34 GMT

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

Re: Maximum Year and Month

sh_12345 — Mon, 09 Jan 2023 09:59:34 GMT

v-zhangti

Re: Maximum Year and Month

v-zhangti — Mon, 16 Jan 2023 07:54:06 GMT

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.

Re: Maximum Year and Month

sh_12345 — Mon, 16 Jan 2023 11:13:11 GMT

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