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:

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

[xifeng_L · Accepted solution]

Hi @TYL001 ,

You can try below column expression.

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

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[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

[xifeng_L · Accepted solution]

Hi @TYL001 ,

You can try below column expression.

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

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[TYL001]

@xifeng_L You. are. AMAZING. Thank you, Thank you!! 🍪

[Greg_Deckler]

@TYL001 I will see if I can give this a try but this looks very much like recursion and DAX hates recursion. It *might* be possible with PREVIOUS and visual calculations.

[TYL001]

@Greg_Deckler Thanks for taking a stab at this.  I was trying to mimic the solution to another post I found (but it had no category), but the code didn't work out (returning NaN). Below is what I tried:

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]
        )