How to get max of value based on date and month

[abc_18]

Hello,

I have a data set which contains opening and closing value for a particular end time, I have to show Max of closing value for a month and a date .

First measure is based on month :-For example, in below data set for month of 'Nov',  Max of End time is 21:00:00.0000  for date '2019-10-30' and closing balance is 5676.43 , so for month of 'NOV' it should give '5676.43'

Second measure is based on Date :- If user selects Date as  '2019-11-25', it should return max value of end time for this  date that is '10687.71.

I have to show these values in a line chart with two dimensions:- Month and Date and above two measures.

So upon selection of Month and date it will display values.

End time	Closing value	Opening value	Date	Financial month
2019-11-25 19:00:00.0000	10646.406	7968.174	2019-11-25	Nov
2019-11-25 19:00:00.0000	13093.776	10809.864	2019-11-25	Nov
2019-11-25 20:00:00.0000	18038.217	13001.079	2019-11-25	Nov
2019-11-25 20:00:00.0000	28487.371	17938.82	2019-11-25	Nov
2019-11-25 21:00:00.0000	6557.804	12594.826	2019-11-25	Nov
2019-11-25 21:00:00.0000	10275.308	17922.32	2019-11-25	Nov
2019-11-25 21:00:00.0000	10687.71	20213.281	2019-11-25	Nov
2019-10-31 21:00:00.0000	5676.43	6543654	2019-11-30	Nov

Please suggest.

[lorenzoconforti]

Can you please explain in more details what type of chart you are looking for? A line chart with two dimensions is possible but I don't think very readable for the data you have

Maybe plot it in Excel so that it clearer

[abc_18]

Hello,

I have attached screenshot here for reference, which display two line charts, first one is for month selection and another one is for date selection.

First line chart should show data based on max of Month along with last entry of 'End time'

Ex:- If user selects month as 'NOV', then max of month is 2019-10-30 along with max of 'End time', single entry it should return.

and second one is based on max of date.

If user selects date as 2015-04-13, then it should return the max of 'End time' entry value for this particular date.

Hope I have explained it clearly.

Regards

[lorenzoconforti]

See attached; I think I understand what you are trying to achieve

I think the problem you have is that you have data points (at least in the sample you have shared) that have the same timestamp; so, sorting them might result in getting the incorrect output (i.e. the wrong last entry)

Probably the best thing is to rely on the load order and possibly create a counter when the script is loaded to make the selection of he last data point easier

Have a look and let me know if this is in the right direction