Extract today's data from the previous date

[Mehay]

Hi all, 

I got stuck with 1 probably small problem, much appreciated if anyone could bring soluation to it.

So basically im building Coronavirus report getting my data from GSSEGIS Jon Hopkins uni.

I have connected through the web and got all three columns confirmed, deaths, and recoevries.

Data they provide is cumulative, thus said today's data of confirmed cases, for example, would include previous dates cases too.

What i need is to substract today's (latest) data from the previous date's data.

e.g. 

26 Aug 2020: 24,176,836 cases

25 Aug 2020: 23,889,150 cases

I NEED:

26 Aug 2020: 24,176,836 - 23,889,150  = 287,686 cases.

So to create a separate column where it would give me cases as of today only without culculating prev days data.

It should be quite easy I am sure, but my brain got so much tired already.

Thanks all in advance.

[lkalawski]

@Mehay

You can use this measure: 

Today Value = SUM(COVID[Column2]) - CALCULATE(SUM(COVID[Column2]),PREVIOUSDAY(COVID[Column1]))

_______________
If I helped, please accept the solution and give kudos! 😀

[Mehay]

@lkalawski 

didn't get what Column2 value is.

[lkalawski]

@Mehay  

Column2 is the cummulative value, as you can see on the screen. You need to adjust naming for your report or please share the screen and I will help you.

_______________
If I helped, please accept the solution and give kudos! 😀

[Mehay]

@lkalawski 

thanks for helping me out.

Sure, thats what I have.

Value column is the cumulative data, so as said prior lateast data would include all data before it too.

*Note: it shows 0 as there was no data for some countries in the begging, im fine with it.

[lkalawski]

@Mehay

Ok, you don't need to add a new column to the table. Upload the data and then create the measure:

Today Value = SUM(COVID[Value]) - CALCULATE(SUM(COVID[Value]),PREVIOUSDAY(COVID[Date]))

Create a visual: table or matrix or chart, add a Date column and Today Value measure there.
This measure will always return a value for the selected day.

_______________
If I helped, please accept the solution and give kudos! 😀

[Mehay]

@lkalawski 

thanks thats great, but wont work in case i want to see new column, i need each date to be subvstract from the prev date till the end of my data which should be till 21st Jan.

Thus said, 26 aug-25 aug data, 25 aug- 24 aug, 24 aug-23 aug, etc.

in that manner i would like to see the data, thats what im looking for.

[v-lili6-msft]

hi  @Mehay 

You could try this logic to create a new calculate column:

Daily Cases = 
var _lastdate=CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Country]),'Table'[Date]<EARLIER('Table'[Date])))
return
'Table'[Value]-CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Country]),'Table'[Date]=_lastdate))

If not your case, please share your sample pbix file and your expected output.

Regards,

Lin

[amitchandak]

@Mehay , you can have date table take last days data and subract it

example - Measure - second one is column

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf])  ),Table[Date]) ,Table[Date],Day)

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

Check  Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Fowmy]

@Mehay 

Apply Below Measure and change the names of the tables and columns:

Current Day Cases = 
VAR D = MAX(Table[Date])
VAR PD=
    CALCULATE(
        MAX(Table[Cases]),
       Table[Date] < D
    )
RETURN

MAX(Table[Cases]) - PD

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Mehay]

@Fowmy 

That formula seems working but giving me different number, as lateast data i got 45k only where is in reality there ~290k cases in 26 Aug.

That's how my data looks in the query editor:

[Fowmy]

@Mehay 

I am not sure how your table is organized; Please refer below link on how to handle the data from John Hopkins:

https://datachant.com/2020/04/03/covid-19-time-intelligence-in-power-bi/

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Mehay]

@Fowmy 

I just showd on the top how my table is organized, it easy to see from the screenshot i just unpivoted daily columns with data, that's all been done.

[Fowmy]

@Mehay 

I created a sample table based on your requirement. You can add the following new column to your table to get the difference between the current and the previous day accumulated cases.

Daily Cases = 
VAR _DATE = [Date]
VAR _CASES = [Cases]
VAR _PREACCCASES = 
    CALCULATE(
        LASTNONBLANKVALUE('Table'[Date],MAX('Table'[Cases])),
        'Table'[Date] < _DATE,
        ALLEXCEPT('Table','Table'[Country])
    )
RETURN
_CASES - _PREACCCASES

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn