topic Re: Way to create a measure using DAX that is capable of adding the value of itself in the past in DAX Commands and Tips

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

STEPH0018 — Fri, 20 Aug 2021 19:10:27 GMT

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!

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

Greg_Deckler — Fri, 20 Aug 2021 19:24:39 GMT

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

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

STEPH0018 — Fri, 20 Aug 2021 19:41:10 GMT

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

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

Greg_Deckler — Fri, 20 Aug 2021 19:44:08 GMT

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

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

STEPH0018 — Sat, 21 Aug 2021 19:50:10 GMT

Here are the data

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

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

Alexrai — Sun, 22 Aug 2021 19:21:06 GMT

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.

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

STEPH0018 — Mon, 23 Aug 2021 12:26:50 GMT

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

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

STEPH0018 — Mon, 23 Aug 2021 12:42:35 GMT

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

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

Alexrai — Wed, 25 Aug 2021 01:53:46 GMT

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

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

Alexrai — Tue, 24 Aug 2021 04:56:27 GMT

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.

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

STEPH0018 — Tue, 24 Aug 2021 14:26:09 GMT

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

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

STEPH0018 — Tue, 24 Aug 2021 16:54:34 GMT

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

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

Alexrai — Wed, 25 Aug 2021 01:53:07 GMT

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:

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

STEPH0018 — Wed, 25 Aug 2021 12:06:52 GMT

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-complex-measure/m-p/2036213#M45681

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

STEPH0018 — Thu, 26 Aug 2021 12:47:29 GMT

Someone would have another idea?

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

Alexrai — Thu, 26 Aug 2021 15:28:48 GMT

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.

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

STEPH0018 — Thu, 26 Aug 2021 15:37:23 GMT

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-complex-measure/m-p/2036213#M45681 I think it might be easier to do, if possible.

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

Alexrai — Fri, 27 Aug 2021 02:55:55 GMT

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