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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.