Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
labuser1235
Helper IV
Helper IV

Subtracting the previous day's value from today's

Hi All,

 

I have taken Coronavirus dataset for practise and would like to Subtracting the previous day's value from today's, to get the increase of cases. 

 

For example:

on 15th March california has 426 confirmed cases and on 16th March california has 557 cases.

New cases on 16th March is 557-426 = 131. 

How can we achieve this ???

I have tried below calculated column as well but of no use.

Below is the screenshot of my data.

 

Column 2 = 
VAR vtoday = 'Corona Log v2'[Name - Copy]
VAR vyesterday = 'Corona Log v2'[Name - Copy]-1
Return
CALCULATE(SUM('Corona Log v2'[Confirmed]),'Corona Log v2'[Name - Copy] = vtoday)-CALCULATE(SUM('Corona Log v2'[Confirmed]),'Corona Log v2'[Name - Copy] = vyesterday)

 

Subtract today's value from yesterdays value.PNG

 

13 REPLIES 13
Yeadeniyi
Regular Visitor

I am trying to figure out the formula to use to return the total new cases in the US when no state is selected in the filter
amitchandak
Super User
Super User

If it is dates then this should work

Column 2 =

CALCULATE(SUM('Corona Log v2'[Confirmed]),'Corona Log v2'[Name - Copy] = today())-CALCULATE(SUM('Corona Log v2'[Confirmed]),'Corona Log v2'[Name - Copy] = today()-1)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak 

 

I have tried as suggested with no luck. Please find the below screenshot.

 

Subtract today's value from yesterdays value_2.PNG

@labuser1235 

Create a measure. If you need as column Have these two

 

max copy = maxx(filter(table,table[Name - Copy]<earlier(table[Name - Copy]) && table[provience/state] =earlier(table[provience/state])),table[Name - Copy])

diff = table[confirmed] -maxx(filter(table,table[max copy]=earlier(table[Name - Copy]) && table[provience/state] =earlier(table[provience/state])),table[confirmed])
or
diff = table[confirmed] -maxx(filter(table,table[Name - Copy]=earlier(table[max copy]) && table[provience/state] =earlier(table[provience/state])),table[confirmed])

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak max copy throws Earlier/Earliest refers to an earlier row context which doesn't exist error. 

 

PBIX I am referring to - Link

 

Max Copy.PNG

Hi, the suggest of Amit is for you to create a measure and not a column. That measure will work directly on visualizations. You can accomplish your objective building a dynamic measure that will change for each day you pick up or adding a column in your dataset. If Amit's suggestion doesn't match what you are trying to do, you can try PREVIOUSDAY(DateColumn) function as CALCULATE filter argument.

 

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

@ibarrau Tried previousday as well still no luck.

@labuser1235 , Please find the attached file

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak This DAX is failing when there is no data in Province/State resulting in negative values. How can we fix this.

 

Negative confirmed values_2.PNGNegative confirmed values.PNG

@labuser1235 

March release has coalesce, use that

coalesce([State],[Country])

 

Earlier might not take it,

so have two formulas like

if(isblank([State]),<In formula use country in place of state>, same formula)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak 

 

Really confused on where to use the coalesce and if(isblank) as we have two formulas. 

 

Could be a little more specific like should I add coalesce in the last copy calculated column or New confirmed column.

Please find the file.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hey, do you have a Calendar Table or are you using the auto-datetime option in Power Bi Desktop options?

It is necessary to use one of these options in order to get time intelligence calculation done. Check my formula and my visualization:

Previousday examplePreviousday example

 

If you have a calendar table you have to replace your Date Column against [Order Date].[Date]. If you don't have one and you are using auto-datetime it should be with the .[Date]

 

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.