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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)

13 REPLIES 13
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
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)

Helper IV

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

Super User

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

Helper IV

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

PBIX I am referring to - Link

Super User

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

Helper IV

@ibarrau Tried previousday as well still no luck.

Super User

@labuser1235 , Please find the attached file

Helper IV

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

Super User

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)

Helper IV

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.

Super User

Please find the file.

Super User

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,

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors