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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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)

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])

 

@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

@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)

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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