Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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)
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)
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
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,
Happy to help!
@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.
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:
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,
Happy to help!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
85 | |
69 | |
49 |
User | Count |
---|---|
141 | |
117 | |
112 | |
59 | |
59 |