DAX Fridays! #167: Calculate previous row using DAX

You have been asking me, how do I calculate previous row in Power BI using DAX so I can get new cases on the corona dataset? , and this is exactly what we are going to do in this video.

Link to previous videos mentioned in this video:

The file is available for download in the download center - dax fridays 167. Please, use this dataset responsibly!!

Keynotes:
00:50 Append all cases type in one column
03:00 Change the financial times calculation for the new column
04:00 Calculate previous row using dax

SUBSCRIBE to learn more about Power and Excel BI!

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:

************

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#daxfridays #curbal #SUBSCRIBE

[JohnSpanos]

Thank you for the video, I was just working on the same calculation myself.

Using the MAX('Overall cases'[Cases]) where the 'Overall cases'[Date] < dates assumes the number of cases does not go down, which it does for the Cruise Ship and a few other cases in the dataset. These cases were moved to other locations.

I suggest changing 'Overall Cases'[Date] < dates to NEXTDAY('Overall Cases'[Date]) = dates which should return the amount of the previous day. This should work because the dataset includes every location for every date once a location is added.

[MrQtip]

Awesome. Thanks for this. I wrote this to get the new cases per day which seemed to work as well:


Confirmed Cases per day = CALCULATE([Cases confirmed]) - CALCULATE('Overall cases'[Cases confirmed], DATEADD('Calendar'[Date], -1, DAY))

[mdhidayat]

Thank you Curbal for this valuable resource!
I am able to apply this to my business problem, which was to find the price change of same product over time.

[abhaydjagtap]

Thank you so much Ruth for video!! You made the complex looking problem simple.

[leandrogalvao]

thanks, i was banging my head on the wall! now i know! congrats on the channel, see it's growing fast!

[egaminek]

I just love you... you make my life easier! Hugs from the UK!!

[dyksonsilva]

Ur videos are just great! The best power bi channel

[princez]

Thank you so much. Its really helpful for my collection report where I compare with the previous collection of the outlet

[ianelder]

Solved another problem I was having - thanks Ruth!!

[priyankaroy]

Can we do this for measures.
I mean for example we will be having measure1 created having values value1, value2.So can we do the previous calculation?

[Ahmad_Askar]

can not really warp my head around this, Ruth can you help me to explain what values those variables take at the beginning and in each evaluation and why did you filter the rows on "Locations, dates and types" to get the previous row data, if it is not too much to ask, and thanks a ton for your videos

[nickroberts]

Thanks so much for this one, I've been looking for how to do this for a while!

[jaydobya]

The formula looks like it will return the previous highest value of [cases] i.e. MAX([cases] where date<thisDate. Why does it return the previous row (albeit with filters) with a MAX operator ?

[mihirsoni]

Thankyou so much for this video, I was looking for this from last few days and now I got this video and I also have subscribed to your channel.

Also I am facing one issue, when I am creating this column for a few set of rows it is working fine (around 400k to 800k rows).
but as my data is a large one and is refreshed daily and around 1M rows are added daily, so running this calculated column on that data is taking a lot of time and after that showing memory error :/
Can you please help me Optimizing this formula or any other way to use the same formula at large dataset?

My Formula:
Energy_consumption =
VAR Dates = FloatTable[DateAndTime]
VAR Index = FloatTable[TagIndex]
VAR Prev_Row = CALCULATE(MAX(FloatTable[Val]), FILTER(FloatTable, FloatTable[DateAndTime] < Dates && FloatTable[TagIndex] = Index ))

RETURN
FloatTable[Val]-Prev_Row

[MrJamesbotes]

This is brilliant thank you! you Explain it so well!

[nenkeishiu]

Hi, I’m very enjoy watching your channel to learn Power BI all the time. Here I have a question which is, how to calculate increment value if the amount been reset as 0 in the middle of somewhere ? Like the sample here if they reset total infection to zero at some date like 1st of March ?

[sanupvarghese]

I have a data which is not cumulative i want to subtract the exact value or previous row. can yo u help?
In this video you used MAX but i do not want MAX but exact value. Everything is working fine expect MAX for me. Baz when there is a dip in the value it does not give me right value in the result

[vabiedward]

Hi Ruth, thank you this is amazing, used this and that of @John Spanos and it works just fine, its my first time in PBI and i am learning, so my next question is how do i get the respective Statsus now, for example getting a measure that gives me the total value of the confirmed, deaths and recovered -I am not quite sure how to write this formula

[KawanExcel]

Hello Ruth, Its always an enlighten everytime I follow your video. Specialy DAX Friday. It helps me a lot.
But I have problem here, and I can not find your video that talk about the VAR.S function
Would you please help to figure out how to use VAR.S ??
I try this mesure but always fail
=CALCULATE(VAR.S(MyTable[MyValue]), FILTER(MyTable, DATESBETWEEN(DimDate[Dates], MAX(DimDate[Dates]) - 30, MAX(DimDate[Dates]))))

Please Help me .... Thank you in advance

[riyajoseph]

hi, I know using Earlier would mean recursion, resulting in a lot of calculations. Does this method provide an advantage over that?