Subtracting the previous day's value from today's

[labuser1235]

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)

[Yeadeniyi]

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]

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)

[labuser1235]

Hi @amitchandak 

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

[amitchandak]

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

[labuser1235]

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

PBIX I am referring to - Link

[ibarrau]

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,

[labuser1235]

@ibarrau Tried previousday as well still no luck.

[amitchandak]

@labuser1235 , Please find the attached file

[labuser1235]

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

[amitchandak]

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

[labuser1235]

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.

[amitchandak]

Please find the file.

[ibarrau]

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 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,