Get last occurrence lower than current month

[talita_verdes]

Hello!

I have a question, and I don't know how to solve it:

Today I have the following data table.

Example:

SALES_DATE	PRODUCT_ID	PRODUCT_DESC	SALES_VALUE
20/04/2021	1	A	10
25/04/2021	1	A	3
13/05/2021	1	A	21
10/03/2021	2	B	11
01/02/2021	2	B	9
10/03/2021	3	C	12
11/05/2021	3	C	13

I need to show the sum of last sales by product as a measure in a table chart, where the month of sale is the last month that had a sale and it isn't in the current month.

Example:

SALES_DATE	PRODUCT_ID	PRODUCT_DESC	SALES_VALUE
01/04/2021	1	A	13
01/03/2021	2	B	11
01/03/2021	3	C	12

Could you please help me?

I tried these approach:

FIRSTSORTEDVALUE(AGGR(SUM({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} SALES_VALUE), PRODUCT_ID), -SALES_DATE)

SUM({<PRODUCT_ID = P({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} PRODUCT_ID)>} SALES_VALUE)

SUM({<SALES_DATE = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(SUM(SALES_DATE), SALES_DATE))

Thanks for your help.

[rubenmarin · Accepted solution]

Hi, I have creted a field that stores he monthsales, becasue I think you want to accumulate by month (the 2nd table has day 1 and product A is addignboth sales of april):

LOAD *, MonthStart(SALES_DATE) as MonthSale INLINE [
    SALES_DATE, PRODUCT_ID, PRODUCT_DESC, SALES_VALUE
    20/04/2021, 1, A, 10
    25/04/2021, 1, A, 3
    13/05/2021, 1, A, 21
    10/03/2021, 2, B, 11
    01/02/2021, 2, B, 9
    10/03/2021, 3, C, 12
    11/05/2021, 3, C, 13
];

For this data, using MonthSale and PRODCUT_ID as dimensions, this expression returns the second table:

SUM({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>} AGGR(If(MonthSale=Max({<MonthSale = {"<$(= MONTHSTART(TODAY()) )"}>}TOTAL <PRODUCT_ID> MonthSale), SUM(SALES_VALUE)), PRODUCT_ID, MonthSale))

[talita_verdes]

Hello  Rubenmarin!

Thank you very much for your help. It works.

While my question had not been answered, I founded another way to solve it, follow below:

I created a master calendar on script, with these fields:

Example:

NUM(%KEY_DATE)                               AS %KEY_DATE,
NUM(%KEY_DATE)                               AS NU_DATE,
DATE(%KEY_DATE)                              AS DATE,
MONTHSTART(%KEY_DATE)            AS MONTH_START_DATE,
MONTH(%KEY_DATE)                         AS MONTH_DATE,
YEAR(%KEY_DATE)                               AS YEAR_DATE

Then, after that app was reloaded, I inserted this measure on table chart:

FIRSTSORTEDVALUE(
                                             AGGR(
                                                           SUM({<MONTH_START_DATE = {"<$(=MONTHSTART(TODAY()))"}>} SALES_VALUE)
                                            , MONTH_START_DATE, PRODUCT_ID)
, - AGGR(MAX(NU_DATE), PRODUCT_ID)
)

Anyway really thanks.