cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

DAX to calculate total using prior calculated total based on category

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

1 ACCEPTED SOLUTION
Frequent Visitor

```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```
Frequent Visitor

```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```