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.
What ifthe dataset doesn't have CASE vaues for every day ? I'm actually looking at something different where there isn't data for every day, but want to do some analysis on "change" of values, e.g. average change to infer "direction".
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))
Hi, can I ask for a bit of help with this syntax? what is the difference between [Cases confirmed] in the first CALCULATE and 'Overall cases'[Cases confirmed] in second?
Branislav Poljasevic. Sorry, It's actually the same thing. I forgot that I created a measure that calculated the sum of overall cases. E.g. Cases confirmed = Sum('overall cases' [confirmed])
Hi, I have an issue with this calculation when trying to add it to a line or bar chart visualization. I want to show the number of new confirmed/deaths/recovered by date and country(the country is optional, I can just add it in a filter and filter the chart on which countries I want to see), I want to create a timeline that shows the evolution of every type of new case per day. The problem is that when adding the new cases per day formula to a line or barchart, it sums every new value tot he previous one. So for example if the number of new cases yesterday was 4 and today is 6, it will not appear as 6 for today, but it will show 10 and it does that for every value until the last one shows the total number of cases related to that date. When looking at the values in a table chart, it shows a row for every new case, so it's correct, from my point of view. I have tried in every way and every formula, it just doesn't show what I want. I want to create a visualization like the ones created here: avatorl.org/covid-19/?page=DashboardNew on the second page, "New Cases by Date", ""New Recoveries by Date". I also want to create a 7 days moving average as in those charts, but I don't really know how. Can you help me with this, please? Thank in advance, have a great day! Thank you for sharing your knowledge!
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.
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
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
Just a question: how many total cases do you obtain with the formula: Total Cases = SUM (Overall Cases[Cases]) I think that with this you add all cases day by day (e.g. day 20=10 cases, day 21=30 cases, total cases = 40 --> Wrong! should be 30 My opinion is that the correct formula is: Nº of Cases = SUMX(SUMMARIZE('Overall Cases';'Overall Cases'[COUNTRY];"CASES";MAX('Overall Cases'[CASES]));[CASES]) It creates a table with only one row per country (and it should be the maximum number of cases) and after all it obtains the sum. Am I right or I did some understanding mistake? Thanks a lot Ruth!!
If day 20 was 10 cases and day 31 was 30 then the number of new cases is 20 and the accumulated is 30. You cant sum a column that has accumulated values you can only take the max of the current row. Does this makes sense? /Ruth
Hola Ruth, gracias a tus vídeos he sido capaz de armar este Dashboard. Que te parece? app.powerbi.com/view?r=eyJrIjoiMzJhYTZlNGMtNzUxZC00NjY0LWI1OGMtMGVmYzhkMmU5ODY5IiwidCI6IjJmZTkxZDU4LTE1ODctNGFlNi05MDMwLTViNTQ4MTcxMmNkNiIsImMiOjl9
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
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 ?
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
Here I am over a year and a half later. This trick to work with earlier row of data is something that I've been looking for. Now if I can just figure out how to fit it into my data set.
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
Explanation is great but you can't find previous row when it contains text or other kind of data, method works only in numbers. In my customer table case I added index to my table which was sequentially increased depending on date. And I used some kind of your method to find maximum index. And that index can be used in any calculation to refer previous row. For example I used Lookup function and I found previous products that customer bought. It showed us which kind of pattern customer follows. But in any way I am benefiting your channel. Keep up the good work. I would ask your additional ways of calculations for my problem (that previous row and even in some point we need to find two lags previous row) because all of these sometime become computationally intensified using our methods.
Hi Curbal, I've been searching everywhere on the internet and how come I can't find anything on how to compare two columns in the matrix in power bi. I either compare (vs) or delta of the two columns in the matrix. If you could pls msg me.
For anyone who is wondering how to get the value from yesterday only - and not the max of all the previous days: Value_Yesterday = VAR previousDate = DATEADD('Table'[Date],-1,DAY) return CALCULATE( SUM('Table'[Value_Today]), FILTER('Table', 'Table'[Date] = previousDate))
Hello! I've been following your videos and all of them are amazing! But I have a question... I wanna calculate the average of the following 3 weeks, group by an atribute, this is an example: Brand|Week|Sales|Calculated sale Sony|1|10|30 Sony|2|20|40 Sony3|30| Sony4|40| Sony5|50| Lenovo1|10|30 Lenovo2|20|40 Lenovo|3|30| Lenovo|4|40| Lenovo|5|50| I would like you to help me Thnks!
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.
You are soooooo right, thanks for sharing!
Pin for more visibility!
/Ruth
Hi, could you explain why nextday() and not previousday() returns the number of cases of the previous day? Thank you!
@@nagaray120 NEXTDAY is used here or PREVIOUSDAY on the VAR. Just a matter of perspective.
@@JohnSpanos that makes so much sense! Thank you so much for your quick response!
What ifthe dataset doesn't have CASE vaues for every day ? I'm actually looking at something different where there isn't data for every day, but want to do some analysis on "change" of values, e.g. average change to infer "direction".
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))
That is the great thing about DAX, there are a lot of ways to calculate stuff,. Well done!
/Ruth
Hi, can I ask for a bit of help with this syntax? what is the difference between [Cases confirmed] in the first CALCULATE and 'Overall cases'[Cases confirmed] in second?
Branislav Poljasevic. Sorry, It's actually the same thing. I forgot that I created a measure that calculated the sum of overall cases. E.g. Cases confirmed = Sum('overall cases' [confirmed])
@@MrQtip1231 Thanks, I suspected that is the case, but better for a newbie to ask just in case :)
Hi, I have an issue with this calculation when trying to add it to a line or bar chart visualization. I want to show the number of new confirmed/deaths/recovered by date and country(the country is optional, I can just add it in a filter and filter the chart on which countries I want to see), I want to create a timeline that shows the evolution of every type of new case per day. The problem is that when adding the new cases per day formula to a line or barchart, it sums every new value tot he previous one. So for example if the number of new cases yesterday was 4 and today is 6, it will not appear as 6 for today, but it will show 10 and it does that for every value until the last one shows the total number of cases related to that date. When looking at the values in a table chart, it shows a row for every new case, so it's correct, from my point of view. I have tried in every way and every formula, it just doesn't show what I want. I want to create a visualization like the ones created here: avatorl.org/covid-19/?page=DashboardNew on the second page, "New Cases by Date", ""New Recoveries by Date". I also want to create a 7 days moving average as in those charts, but I don't really know how. Can you help me with this, please? Thank in advance, have a great day! Thank you for sharing your knowledge!
Thank you so much Ruth for video!! You made the complex looking problem simple.
You are so welcome!
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.
Music to my ears :)
Hi @mdhidayat5706
Please can you share me the DAX you use, i'm having this issue for my calculation and i haven't got solution to it yet.
Thanks
I got it!
Thank you so much, Ruth. God Bless You!
The way of presentation is awesome
Thanks!
/Ruth
Ur videos are just great! The best power bi channel
Thanks 🥳🥳🥳
/Ruth
I just love you... you make my life easier! Hugs from the UK!!
thanks, i was banging my head on the wall! now i know! congrats on the channel, see it's growing fast!
Thanks Leandro!
/Ruth
Thank you so much. Its really helpful for my collection report where I compare with the previous collection of the outlet
This is brilliant thank you! you Explain it so well!
Thanks 😊
Thanks for such a nice presentation
Thanks so much for this one, I've been looking for how to do this for a while!
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
Solved another problem I was having - thanks Ruth!!
Pleasure :)
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?
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
Just a question: how many total cases do you obtain with the formula:
Total Cases = SUM (Overall Cases[Cases])
I think that with this you add all cases day by day (e.g. day 20=10 cases, day 21=30 cases, total cases = 40 --> Wrong! should be 30
My opinion is that the correct formula is:
Nº of Cases =
SUMX(SUMMARIZE('Overall Cases';'Overall Cases'[COUNTRY];"CASES";MAX('Overall Cases'[CASES]));[CASES])
It creates a table with only one row per country (and it should be the maximum number of cases) and after all it obtains the sum.
Am I right or I did some understanding mistake?
Thanks a lot Ruth!!
If day 20 was 10 cases and day 31 was 30 then the number of new cases is 20 and the accumulated is 30. You cant sum a column that has accumulated values you can only take the max of the current row.
Does this makes sense?
/Ruth
@@CurbalEN Yes, you are right, but it is not the formula on minute 3:14
The formula looks like it will return the previous highest value of [cases] i.e. MAX([cases] where date
Thanks Ruth. Great solution.
Check the pinned comment for a small amendment in the calculation!
/Ruth
Hola Ruth, gracias a tus vídeos he sido capaz de armar este Dashboard. Que te parece?
app.powerbi.com/view?r=eyJrIjoiMzJhYTZlNGMtNzUxZC00NjY0LWI1OGMtMGVmYzhkMmU5ODY5IiwidCI6IjJmZTkxZDU4LTE1ODctNGFlNi05MDMwLTViNTQ4MTcxMmNkNiIsImMiOjl9
Genial!
/Ruth
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
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 ?
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
i used Confirmed = CALCULATE(sum(All_Cases[Cases]),All_Cases[Status]="Confirmed"), is this correct?
Here I am over a year and a half later. This trick to work with earlier row of data is something that I've been looking for. Now if I can just figure out how to fit it into my data set.
hi, I know using Earlier would mean recursion, resulting in a lot of calculations. Does this method provide an advantage over that?
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
Thank you so much
Thank you Ruth!
You welcome!!
/Ruth
Explanation is great but you can't find previous row when it contains text or other kind of data, method works only in numbers. In my customer table case I added index to my table which was sequentially increased depending on date. And I used some kind of your method to find maximum index. And that index can be used in any calculation to refer previous row. For example I used Lookup function and I found previous products that customer bought. It showed us which kind of pattern customer follows. But in any way I am benefiting your channel. Keep up the good work. I would ask your additional ways of calculations for my problem (that previous row and even in some point we need to find two lags previous row) because all of these sometime become computationally intensified using our methods.
It doest work with text due to the MAX function. Change that depending of what you want to do!
/Ruth
brilliant!
Great job!
😊
/Ruth
Hi Curbal,
I've been searching everywhere on the internet and how come I can't find anything on how to compare two columns in the matrix in power bi. I either compare (vs) or delta of the two columns in the matrix. If you could pls msg me.
Cool 💛
Naji K.
🔥🔥🔥
/Ruth
For anyone who is wondering how to get the value from yesterday only - and not the max of all the previous days:
Value_Yesterday =
VAR previousDate = DATEADD('Table'[Date],-1,DAY)
return
CALCULATE(
SUM('Table'[Value_Today]),
FILTER('Table',
'Table'[Date] = previousDate))
Awesome!
In the future, please link directly to the file in question. Don't need a link to whole folder.
Hello! I've been following your videos and all of them are amazing! But I have a question...
I wanna calculate the average of the following 3 weeks, group by an atribute, this is an example:
Brand|Week|Sales|Calculated sale
Sony|1|10|30
Sony|2|20|40
Sony3|30|
Sony4|40|
Sony5|50|
Lenovo1|10|30
Lenovo2|20|40
Lenovo|3|30|
Lenovo|4|40|
Lenovo|5|50|
I would like you to help me
Thnks!
je viens de voir ta video et.... youtube me met une pube sur une formation pour etre ecrivain ! MDR
🤯 👌🙏🙏
🔥🔥🔥
/Ruth
Creo que puede ser una buena solucion también para crear filas acumuladas en una matriz. 🙂