Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
How to get a value with max month and another criteria as below?
=sum(if([CPR_TM1_202202.MONTH]= '2/1/2022' and [CPR_TM1_202202.Business]='CMB', [CPR_TM1_202202.amount]/1000000000))
2/1/2022 is max date in my data set. I want to change '2/1/2022' using the MAX function
any idea?
Try this, I removed Date function:
SUM({<[CPR_TM1_202202.MONTH]={"$(=max([CPR_TM1_202202.MONTH]))"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)
if you use the date max(date)-1 will return the previous day.
So for YYYYMM to get you can use max(yearmonth, 2) - previous month (second biggest value)
for MM/DD/YYYY you can use date(max(date)-1) - previous day
max function returns values in numeric format, so it's important to convert value back to date format.
ahhh i seee.
really appreciate all solutions from you!
thank you Vitalli!
Regards,
Daniel
Hi,
You can use the following expression
SUM({<[CPR_TM1_202202.MONTH]={"$(=Date(max([CPR_TM1_202202.MONTH])))"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)
Regards,
Vitalii
Hi Vitalii,
Thanks for your help.
the syntax is no error, but the value not show
Regards,
Daniel
Could you send a screenshot of your result?
For instance, my source is following
Expression:
Result:
Regards,
Vitalii
I use this,
=sum(if([CPR_TM1_202202.MONTH]= Max( total [CPR_TM1_202202.MONTH]) and [CPR_TM1_202202.Business]='CMB', [CPR_TM1_202202.amount]/1000000000))
it's worked
Try this, I removed Date function:
SUM({<[CPR_TM1_202202.MONTH]={"$(=max([CPR_TM1_202202.MONTH]))"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)
this is work!
if I want to back 1 month for max date, should I put ,-1?
example:
SUM({<[CPR_TM1_202202.MONTH]={"$(=max([CPR_TM1_202202.MONTH]),-1)"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)
It will work with max([CPR_TM1_202202.MONTH], 2)
not -1?
if max is 2/2/2022 then i need to substract with -1, right?
if you use the date max(date)-1 will return the previous day.
So for YYYYMM to get you can use max(yearmonth, 2) - previous month (second biggest value)
for MM/DD/YYYY you can use date(max(date)-1) - previous day
max function returns values in numeric format, so it's important to convert value back to date format.