topic Re: DAX to calculate total using prior calculated total based on category in DAX Commands and Tips

DAX to calculate total using prior calculated total based on category

TYL001 — Fri, 17 May 2024 14:38:53 GMT

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

Re: DAX to calculate total using prior calculated total based on category

Greg_Deckler — Fri, 17 May 2024 14:49:29 GMT

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.

Re: DAX to calculate total using prior calculated total based on category

TYL001 — Fri, 17 May 2024 15:06:49 GMT

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

Re: DAX to calculate total using prior calculated total based on category

xifeng_L — Fri, 17 May 2024 16:12:50 GMT

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~

Re: DAX to calculate total using prior calculated total based on category

TYL001 — Fri, 17 May 2024 16:27:46 GMT

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

Re: DAX to calculate total using prior calculated total based on category

TYL001 — Fri, 17 May 2024 16:29:29 GMT

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