Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Last purchase value

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.

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    @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