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:

 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
2 ACCEPTED SOLUTIONS
Super User

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~

Microsoft MVP of PowerBI

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```
5 REPLIES 5
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```
Super User

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~

Microsoft MVP of PowerBI

Frequent Visitor

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

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

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.