Forum Discussion
Helper IExtract today's data from the previous date
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.
13 Replies
- Fowmy
Super User
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 🙂
- 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 🙂
- 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-5c3243d1f9To 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
