3 month rolling calculation error

[Anonymous]

Hi,

I have a 2 data sources and need to do some operations between them. I have created a sample to represent the real life solution that I need.

Table Sales:

dateSold	Sector	Idsale	NPS
01/02/2022	A	1	good
01/01/2022	A	2	bad
01/01/2022	A	3	good
01/01/2022	B	4	bad
01/12/2021	A	5	bad
01/12/2021	B	6	good
01/12/2021	B	7	good
01/11/2021	A	8	good
01/11/2021	A	9	good
01/11/2021	A	10	good

Table Weights:

Sector	Year	Weight
A	2021	0,7
B	2021	0,3
A	2022	0,8
B	2022	0,2

Below are all the variables that I created:

good = COUNTROWS(
        CALCULATETABLE(Sales
            , DATESINPERIOD(Sales[dateSold],SELECTEDVALUE('Calendar'[MonthYear]),-3,MONTH)
            , Sales[NPS] == "good"
)    )

bad = 
COUNTROWS(
        CALCULATETABLE(Sales
            , DATESINPERIOD(Sales[dateSold],SELECTEDVALUE('Calendar'[MonthYear]),-3,MONTH)
            , Sales[NPS] == "bad"
)    )

total = COUNTROWS(
        CALCULATETABLE(Sales
            , DATESINPERIOD(Sales[dateSold],SELECTEDVALUE('Calendar'[MonthYear]),-3,MONTH)
)    )

NPSCalculated = DIVIDE([good],[total])-DIVIDE([bad],[total]) 

And to connect the tables I did this on both tables:

WeightsKey = CONCATENATE(Weights[Year], Weights[Sector])

And the connection is like this:

I have already created a calculated measure that counts sales for the last 3 months, and consider all the sectors in this period.
But now I need to to calculate this measure by month and multiply it by the weight of that year. This is what I got and is not working:

FinalValue = 
VAR BASE = CROSSJOIN ( VALUES ('Calendar'[MonthYear]), VALUES(Weights[Sector]) )

VAR BASE2 = ADDCOLUMNS(BASE, "Ano", YEAR('Calendar'[MonthYear]))

VAR BASE3 = NATURALINNERJOIN(BASE2,Weights)

VAR BASE4 = ADDCOLUMNS(BASE3, "NPS_FINAL", [Weight] * [NPSCalculated])

RETURN
    SUMX ( BASE4, [NPS_FINAL] )

This is returning 0,33 for 2022-02 as example, and it should be returning 0,067 acording to the table below:

Sector	bad	good	Total	NPSCalculated	Weight	Final Value
A	2	2	4	0,00	0,8	0
B	1	2	3	0,33	0,2	0,067

Below I leave an example of what is happening on the background with some things right and some wrong:

The operation for 2021-12 completely right, but for 2022-02 it is considering 2021 weight, which it shouldnt be considered. Also, note that 2022-02 has no Sector B on the table, as it should consider the previous appeareances of B in the previous 3 months, so this is also right.

This is the final table and result I will use, and as I said, 2021-12 is right, but every other MonthYear that the period of 3 months before include another year is broken.

I know it looks like a lot, but I'm very close to the solution, could someone please help me??

[amitchandak]

@Anonymous , You join it with the calendar table marked as date table and create measure like

example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

[Anonymous]

Hi @amitchandak, thank you for your reply!

Actually, there is no issue on calculating the rolling 3 period, the issue is on multiplying it by the respective weight of its Sector, like I'm trying to do in FinalValue field.

The rolling 3 is working fine, as done by the variables BAD, GOOD and TOTAL.