Previous 3 Moths Dynamic Set Analysis

[CalvinTosen]

Hi Everyone.

I would appreciate any assistance with this expression.

I need to calculate the sales for each product three months before the product's last procurement date.

Sum({<Transaction_Date={">=$(=MonthStart(Max([Pro_Date]), -3))<=$(=MonthEnd(Max([Pro_Date]), -1))"}>} [LINE.NETT.VALUE] )

This works when 1 product code is selected but when multiple product codes are selected it uses the highest max(Pro_Date) for each product code.
Apologies I'm not allowed to show the sales values

Apologies I am not allowed to post the sales values.

 In this case, instead of calculating three months back from 2023/05/16 for product code 490843, it's calculating the previous three months from 2024/09/04 for both of them.

If anyone has any solutions it would be greatly appreciated and if any more information is required please let me know, I can provide dummy data if its required.

[Kushal_Chawda]

@CalvinTosen  Set analysis works once per chart. It doesn't calculated row by row. Hence, it will not consider max values for individual product but max from all product. Try below expression

sum({<[LINE.NETT.VALUE]  = {"=aggr(Transaction_Date >= monthstart(max(total <Product>[Pro_Date]),-3) and [Transaction_Date <= monthend(max(total <Product>[Pro_Date]) ,-1),Product, Transaction_Date)"}>}[LINE.NETT.VALUE])

[CalvinTosen]

Hi Kushal

Thank you so much for replying.

Unfortunatly is does not seem to be working, it is giving 0 values.

[Kushal_Chawda]

@CalvinTosen  make sure that you are using correct field names for Sales, Product and Dates. Is your measure giving any error? Please share screenshot of your measure

[CalvinTosen]

Field Names are [Product Code no Branch] for the products

                            [LINE.NETT.VALUE] for the Sales

                            Transaction_Date for the Sales Dates

                            Pro_Date for the procurement date
Original measure 
Sum({<Transaction_Date={">=$(=MonthStart(Max([Pro_Date]), -3))<=$(=MonthEnd(Max([Pro_Date]), -1))"}>} [LINE.NETT.VALUE] )

Proposed solution with field name changes
sum({<[LINE.NETT.VALUE] = {'=aggr(Transaction_Date >= monthstart(max(total <[Product Code No Branch]>[Pro_Date]),-3) and [Transaction_Date <= monthend(max(total <[Product Code No Branch]>[Pro_Date]) ,-1),[Product Code No Branch], Transaction_Date)'}>}[LINE.NETT.VALUE])

The proposed solutionThe original

Let me know if you require anything else

[Kushal_Chawda]

@CalvinTosen  It seems you are using procurement date in dimension so you might need to add that as well in aggr function. If it doesn't work please share sample data with expected output

sum({<[LINE.NETT.VALUE]  = {"=aggr(Transaction_Date >= monthstart(max(total <Product>[Pro_Date]),-3) and [Transaction_Date <= monthend(max(total <Product>[Pro_Date]) ,-1),Product, Transaction_Date,[Pro_Date])"}>}[LINE.NETT.VALUE])

[CalvinTosen]

My apologies for taking so long Kushal I quickly made a dummy app. 
I marked where it is currently summing the LINE.NETT.AMOUNT for both product codes since 490930's latest Pro_Date is in September and I marked in Green the values that 490843 is supposed to use. Hope this Helps

[Kushal_Chawda]

@CalvinTosen  Can you share demo app? Without looking at data model it will be hard to say what's wrong

[CalvinTosen · Accepted solution]

Hi Kushal 

I believe I found a solution this expression seems to be working:

Sum(
Aggr( nodistinct
If(
[Transaction_Date] >= MonthStart(Max(TOTAL <[Product Code No Branch]> [Pro_Date]), -3)
and [Transaction_Date] <= MonthEnd(Max(TOTAL <[Product Code No Branch]> [Pro_Date]), -1),
[LINE.NETT.VALUE]
),
[Product Code No Branch], [Transaction_Date],[DOC.NO]
)
)

Thank you so much for the assistance without your expression I would never have thought of the idea to use Aggr like this.

The [DOC.NO] is the invoice number that allows it to use duplicate [LINE.NETT.VALUE]s .