Last purchase value

[Syndicate_Admin]

Hello tod@s, I have the following scenario and problem, with the following two measures:

Measure Last Purchase Date:

Fecha_Ultima_Compra = LASTNONBLANK(Calendar[Date],[Valor_Precio_Unit_Final])

Measure Amount Last Purchase:

Monto_Ultima_Venta 2 = LASTNONBLANKVALUE(Calendar[Date],[Valor_Precio_Unit_Final])

With these two measures I am looking to obtain the last purchase value of any item, for this I am also using two segmenters one to filter by month and another to filter some particular item.

My problem is the following, when filter by month and by item, the table shows you the last value and date of purchase, according to the filtered month, however, if I ask for all the months, the table only shows the last value and date of purchase of the year of that filtered item, what I want is that in the table it shows you the last values and date of purchase of each month.

PS: the month of July still has no purchase of the selected item, maybe in other items yes.

I appreciate the help.

[Syndicate_Admin]

@Syndicate_Admin , I hope you are well,

I'm sorry it doesn't work for me, given the following:

• I use a table called Calendar: "Calendar = CALENDAR(MIN(Compras_Detallada[f_fecha]), MAX(Compras_Detallada[f_fecha]))".
• I have a relationship between two Tables: Compras_Detallada [f_fecha] & Calendar [Date].
• To segment or filter dates I use the calendar table, in this case the segmenter is Calendar [Month].

I attach my . Pbix

[Anonymous]

Hi  @Syndicate_Admin ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Calendar =
DISTINCT('Table'[Month])

2. Create measure.

Flag =
var _selectmonth=SELECTCOLUMNS('Calendar',"month",[Month])
var _selectitem=SELECTEDVALUE('Table'[item])
var _maxdate=MAXX(
    FILTER(ALL('Table'),
    'Table'[Month] in _selectmonth && 'Table'[item]=_selectitem&&'Table'[Month]=MAX('Table'[Month])),[Date])
return
IF(
    MAX('Table'[Date]) =_maxdate,1,0)

 3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[rubayatyasmin]

Hi, @Syndicate_Admin 

you need to modify the measures to consider both the item and the month context when calculating the last purchase date and amount.