Calculating a value based on max of A per month.

[PaulusD]

Hi,

I'm trying to return a result from a dataset but am not getting the output i'm looking for, and am in need of some help.

I have a set with the following information:

What I'm trying to find is the NET_AVAIL_QTY at the end of the month.

To do this, I need to lookup the value related to the last day of the month, and on that date with the highest MRP_NET_SEQ number.

So from the example above, the output would be 66 (highest MRP_NET_SEQ number from the 30th of september).

There is a date table in the model.

Thanks in advance.

[amitchandak · Accepted solution]

@PaulusD , Try measure like

If you always have last date of month
measure =
var _1 = closingbalancemonth(max(Table[MRP_NET_SEQ]),Date[Date])
return
calculate(closingbalancemonth(Sum(Table[NET_AVAIL_QTY ]),Date[Date]), filter(Table, Table[MRP_NET_SEQ] =_1))

or

measure =
var _1 = calculate(lastnonbalnkvalue(Date[Date], max(Table[MRP_NET_SEQ])), filter(allselected('Date'), 'Date'[Month Year] = max('Date'[Month year])))
return
calculate(Sum(Table[NET_AVAIL_QTY ]), filter(Table, Table[MRP_NET_SEQ] =_1))

Use date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PaulusD]

Hi @amitchandak ,

The second option did exactly what I asked. Unforntunately it uncovered a new problem but that's not something I can solve with the above question ;-).

Thanks for the quick response!