Way to create a measure using DAX that is capable of adding the value of itself in the past

[STEPH0018]

Hey guys!
I was wondering if there was any way I could create a measure using DAX that is capable of adding the value of itself in the past.

The first instance of the variable “Measure”, represented by the letter “a” in the yellow column, would be equal to 0. In the following instances, the value of the variable “Measure” should be equal to the value of the “Contribution” variable plus the “Measure” variable in the previous instance multiplied by the “Divisaocota” variable of the instance divided by the “Divisaocota” variable of the previous instance.

Here is an example of what I mean:

Measure0 = 0

Measure1 = Contribution1 + Measure0 (Divisaocota1/Divisaocota0)

Measure2 = Contribution2 + Measure1 (Divisaocota2/Divisacao1)

How would I go about doing this?

Thanks so much guys!

[Alexrai]

You might want to give an index a try in PowerQuery, sorting first as required (Name, Date). Then it's much simpler to add the current measure to CALCULATE([MyMeasure],  Table[Index]=PreviousIndexVariable) while checking that the Client IDs still match.

[STEPH0018]

I think about making each "name" separate, because then I wouldn't have this problem, but even so when it comes to making the account for only one "name", I'm having the same problem

[STEPH0018]

Hi, Alexrai, I already tried to do this, but it didn't work.

[Alexrai]

Greg may post a better solution, but I would try solve it like this after sorting by ClientID and then DATE in PowerQuery and adding an index column:

VAR Client = Client[ClientID] - Get ID of current client

VAR IndexStart= CALCULATE(MIN(FactTable[Index]), FactTable[ClientID]=Client))

-- Now we have the starting index and clientID

VAR CurrentIndex = FactTable[Index] -- Index of the current row in the matrix to work backward from

VAR PreviousClientDate = LOOKUPVALUE(FactTable[Date], FactTable[Index], CurrentIndex-1)) -- Get the value of the date of previous entry and use it to filter the fact table

VAR CurrentTotal = FactTable[Contribution] + CALCULATE(SUM(FactTable[Contribution]), FactTable[Date]=PreviousClientDate, FactTable[ClientID] = ClientID) -- Add current contribution to the contribution belonging to the prior index ID

RETURN

IF(CurrentIndex = IndexStart, 0, CurrentTotal) - If we are at the first index number for the client then return a zero

Another option without an index would be to try something along the line of:

Add Contributions =

VAR ClientName = MAX(Facts[Name])

VAR CurrentDate = MAX('Date'[Date])

VAR PriorStart = CALCULATE(MIN(Facts[Date]), Facts[Name] = ClientName, Facts[Date]< CurrentDate, REMOVEFILTERS('Date'[Date]))

VAR PriorEnd = CALCULATE(MAX(Facts[Date]), Facts[Name] = ClientName, Facts[Date]< CurrentDate, REMOVEFILTERS('Date'[Date]))

VAR PriorValue = CALCULATE(SUM(Facts[Contribution]), Facts[Name]=ClientName, DATESBETWEEN(Facts[Date], PriorStart, PriorEnd), REMOVEFILTERS('Date'[Date]))

VAR CurrentValue = SUM(Facts[Contribution])

VAR NewValue = CurrentValue + PriorValue

RETURN IF(ISBLANK(PriorStart), CurrentValue, NewValue)

You can reach into the previous row by filtering for all dates less than the date in the current row context and then taking the maximum, then using that to filter the fact table for the value you need and adding it to the current value.

Let me know if that helps

[STEPH0018]

Hi, Alexrai, unfortunately it didn't work. The calculation did not accumulate.

[Alexrai]

Sorry may have been a bit late to be winging it, I created a non-indexed measure to get a cummulative total of contributions. The idea is the same. If you need to add multiplication by those other fields then you only need to pull values for CurrentDate and PriorEnd which can be accomplished with calculate.

Add Contributions =

VAR ClientName = MAX(Facts[Name])

VAR CurrentDate = MAX('Date'[Date])

VAR PriorStart = CALCULATE(MIN(Facts[Date]), Facts[Name] = ClientName, Facts[Date]< CurrentDate, REMOVEFILTERS('Date'[Date]))

VAR PriorEnd = CALCULATE(MAX(Facts[Date]), Facts[Name] = ClientName, Facts[Date]< CurrentDate, REMOVEFILTERS('Date'[Date]))

VAR PriorValue = CALCULATE(SUM(Facts[Contribution]), Facts[Name]=ClientName, DATESBETWEEN(Facts[Date], PriorStart, PriorEnd), REMOVEFILTERS('Date'[Date]))

VAR CurrentValue = SUM(Facts[Contribution])

VAR NewValue = CurrentValue + PriorValue

RETURN IF(ISBLANK(PriorStart), CurrentValue, NewValue)

Output and model:

[STEPH0018]

Someone would have another idea?

[Alexrai]

I downloaded the pbix but have been busy, I think it might be easier to have the values you need to run a cummulative sum on stored in a table rather than calculated in PowerBI as a measure but I'll try find some time this evening if no-one responds before then.

[STEPH0018]

it is not possible to make the accumulated before because it depends on the data that the user chooses. Thank you very much. I put the data in another way on this link: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-get-data-from-previous-row-with-comple...   I think it might be easier to do, if possible.

[Alexrai]

Give this a try: BUT, for the code to work you'll need to use R[Data], R[Attributo] in the table visual and remove dCalandario and Names, then sort by R[Attributo]. It takes the past value of W[Valor] adds it to the current R[Valor] and multiplies by (Divisaocota2 / Divisaocota1).

I can't stress the importance of having a fact table and dimensions in a Star Schema form so that PowerBI filters work correctly. "Name" in your model has no filter connected to COTA so the measure is not going to evalulate correctly in the context of the Name Table.  The calander table needs to have consecutive dates or DATEADD is not going to work properly either.

CSum =

VAR CurrentDate = MAX(R[Data])

VAR CurrentName = MAX(R[Atributo])

VAR StartDate = CALCULATE(MIN(R[Data]), R[Data] < CurrentDate, R[Atributo]=CurrentName)

VAR PriorDate = CALCULATE(MAX(R[Data]), R[Data] < CurrentDate, R[Atributo]=CurrentName)

VAR CurrentValue = MAXX(CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(R, R[Data], R[Atributo]), "Contribution1", SUM(R[Valor])), R[Atributo]=CurrentName, dCalendario[DATA]=CurrentDate), [Contribution1])

VAR PriorValue = MAXX(CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(W, W[Data], W[Atributo]), "Contribution2", SUM(W[Valor])), W[Atributo]=CurrentName, dCalendario[DATA]=PriorDate), [Contribution2])

VAR CurrentDiva = CALCULATE([divisaocota], dCalendario[DATA]=CurrentDate)

VAR PriorDiva = CALCULATE([divisaocota], dCalendario[DATA]=PriorDate)

RETURN

IF(NOT ISBLANK(PriorValue), (CurrentValue + PriorValue) * CurrentDiva / PriorDiva )

Hopefully that gets you close to where you need to be ^^ 

[STEPH0018]

Thaks, Alexrai, but see, the first value is not zero, for the first date chosen by the user. I separated the data by name, I think it's easier, but I still haven't found a solution. what do you think of this question? https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-get-data-from-previous-row-with-comple... 

[STEPH0018]

Hi, Alexrai, I will try to do this and return here if I can. Thanks 

[Alexrai]

I'm just looking at the data quickly - if you don't have your model setup properly it's hard to use DAX. You'll need at least a calander table with consecutive dates marked as a date table, and that should be linked with a one to many relationship to a fact table in tabular format with each record like: ClientName, Contribution Amount, Date, whatever other column.

[Greg_Deckler]

@STEPH0018 There is a way but it is incredibly ugly. Previous Value (“Recursion”) in DAX – Greg Deckler

[STEPH0018]

Hi Greg, I had seen your publication before and tried to do something similar with my data, but when replacing the "Value" I ended up not succeeding 

[Greg_Deckler]

@STEPH0018 Paste me some data as text and I can give it a whirl.

[STEPH0018]

Here are the data

https://drive.google.com/drive/folders/1FXwuGd_HA1sOJn5aiFX6iDoXHIYLX51S?usp=sharing