DAX to calculate total using prior calculated total based on category

[TYL001]

Hi - 

I have below data in Table View and need a Dax formula to compute a column with the total based on prior week's computed number (prior total * factor) for each category:

Table: DATA

Category	Date	factor	Total	calculation
A	01/01/24	-	100
A	01/08/24	1.01682	101.68	(100*1.01682)
A	01/15/24	1.01734	103.45	(101.68*1.01734)
A	01/22/24	1.01939	105.45	(103.45*1.01939)
A	01/29/24	1.01854	107.41	(105.45*1.01854)
B	01/01/24	1.01792	50
B	01/08/24	1.01612	50.81
B	01/15/24	1.01332	51.48
B	01/22/24	1.01134	52.07
B	01/29/24	1.01166	52.67

The code I've tried and failed (NaN) is below:

CALCULATION = 
VAR _MINVALUE = 
DATA[Total]
    *CALCULATE(
        SUM(DATA[factor]),
        FILTER(DATA,DATA[Date]=MIN('DATA'[Date]])&&DATA[Category]=DATA[Category])
    )
        RETURN
        IF(
            DATA[Date]=MIN('DATA'[Date]])&&DATA[Category]=DATA[Category],
            _MINVALUE,
            CALCULATE(
                PRODUCT(DATA[factor]),
                FILTER(DATA,DATA[Date]<=EARLIER(DATA[Date])&&DATA[Category]=DATA[Category])
        )*DATA[factor]
        )

 Thanks in advance.

[TYL001 · Accepted solution]

Answered!

Column = 
VAR TempTable = FILTER('Table','Table'[Category]=EARLIER('Table'[Category]) && 'Table'[Date]<=EARLIER('Table'[Date]))
VAR FirstVal = MAXX(TOPN(1,TempTable,'Table'[Date],1),'Table'[Total])
RETURN
PRODUCTX(
    TempTable,
    VAR MinDateOfCat = MINX(TempTable,'Table'[Date])
    RETURN
    IF('Table'[Date]=MinDateOfCat,1,'Table'[factor])
)*FirstVal