looping using DAX

[Anonymous]

Hello Everyone,

I am very new with power BI and please I need help from expert.

I need the DAX translation of this For Loop:

a = 0;
b = 0;

for (int i=1; i<=n; i++)
{
a = x - b;
b = x;
}

In this loop, x and n are a predefined values that I will get from a table.

please help.

[parry2k]

@Anonymous finally, so glad to hear it. Cheers!!

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous did you marked your date table as the date and also when you are visualizing the data, are you using date column from the date table.

[parry2k · Accepted solution]

@Anonymous this is a simple time intelligence calculation, and as a best practice, it is recommended to add a date dimension to your model to work with dates, and here is one of my post that talk about how to add a date dimension.

Once it is done, just add the following measures, this can be done in one measure too, but I like to break these down for better understanding and also to reuse these measure (if required)

Sum Current = SUM ( Table[Current] )

Sum Prev Day = CALCULATE ( [Sum Current], DATEADD ( 'Calendar'[Date], -1, DAY ) )

Increase = [Sum Current] - [Sum Prev Day]

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thank you so much for your help. this actually worked.

[Anonymous]

Here is my code:

Prev Day = CALCULATE( SUM(HBLReport[Current]), DATEADD('Date'[Date], -1, DAY) )

and the result is the same as column Current. it doesn't.

[Anonymous]

Hello,

I added the date dimension as you said but still having a problem with the second line of the code,

the DATEADD() function. When I type that lene, Power BI does not ecognize 'Calendar'[DATE]. I eplaced Calendar with my table name and DATE with my date column but still not working.

[Anonymous]

I will try this tomorrow and let you know what I got.

Thank you so much for your help.

[parry2k]

@Anonymous Is that the only column, Current. How do you know the order? Is the order base from small to big or there is a date or index column?

[Anonymous]

yes it is ordered based on the date.

    Date

12/1/2020

12/2/2020

12/3/2020

12/4/2020

12/5/2020

and so on.

[parry2k]

@Anonymous I'm wondering if this is really the correct approach. By looking at your requirement, seems like maybe looping is not required. You started the post with the looping requirement, but I think if you share your sample data and expected output, it is probably a much simpler solution. 

[Anonymous]

Sorry, I started my post with looping because I thought it was the best way to solve the issue. I posted a table with the sample of data I am looking to do.

Thank you.

[parry2k]

@Anonymous can you share the code you are using in the advanced editor?

[Anonymous]

this is basically what I need to do: in this table, I have the values for CURRENT and I need a code to calculate INCREASE, which is the difference between CURRENT for the current day and the day before. Not to forget that the size of the table is not fixed. that is why I was using a loop from 1 to the actual size.

CURRENT              INCREASE
    63                            63
   135                           72
   212                            77
   290                            78
   350                            60

Thank You

[Anonymous]

I found something that could be easier if I understand how it works. this method is using calculated column and here is the code:

For Loop =
// Provide some starting values
VAR __n = 5
VAR __sum = 0
// Generate a "loop table", this will emulate a for loop for i=1 to some number
VAR __loopTable = GENERATESERIES(1,__n)
// Add in our calculated sum, emulating calculations done as iterations over the loop
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + SUMX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value]))
// Determine our MAX interation, the maximum value for "i"
VAR __max = MAXX(__loopTable1,[Value])
RETURN
// Return the value associated with the maximum value of "i" which is the last iteration in our "loop"
MAXX(FILTER(__loopTable1,[Value]=__max),[__sum])

My problem now is that I need to adapt this part: 
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + SUMX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value]))
so that it will do the calculation I want.

thanks

[Anonymous]

I am trying to apply List.Accumulate function but still not working. Maybe I am not using it the right way. what I am doing is I go to advanced query  and I type in the function but something is still wrong. 

[mahoneypat]

You really can't do recursive calculations in DAX.  You can do it with List.Accumulate in the query editor though.

List.Accumulate Hidden Gem of Power Query List Functions in Power BI - RADACAD

Regards,

Pat