Converting daily aggregated COVID data to an accurate Monthly/daily report

[Anonymous]

I am trying to do some analysis on Covid 19 cases .  The problem is my data set does not show new cases each day but the  total cases accumulated to date.  For example on January 23rd there were 102 new Covid cases but the chart below shows January 23rd had a total of 702 cases. I am building a dashboard with a monthly view and a daily view. But my monthly numbers are being grossly overstated. How can I get an accurate count of monthly cases and how can I identify the daily  increase of new cases?

Thank you for your help

[az38]

@Anonymous 

you can try a measure like

Today Cases = 
MAX(Table[Cases]) - 
CALCULATE(MAX(Table[Cases]), FILTER(ALL(Table), Table[Date] = MAX(Table[Date]) -1) )

[Anonymous]

Thank you @az38  for your help. I made the adjustments you suggested as referenced below. But I didnt get the results I was looking for as referenced in the below chart. Did I do something wrong? any suggestions on how I can proceed?

Today Cases =

MAX('Covid-19 Tracker'[Cases]) -

CALCULATE(MAX('Covid-19 Tracker'[Cases]), FILTER(ALL('Covid-19 Tracker'), 'Covid-19 Tracker'[Date] = MAX('Covid-19 Tracker'[Date]) -1) )

Date	Cases	Today   Cases
1/22/2020	600	444
1/23/2020	702	0
1/24/2020	1003	105
1/25/2020	1515	212
1/26/2020	2226	297
1/27/2020	3070	365
1/28/2020	5816	2131
1/29/2020	6425	0
1/30/2020	8548	1349
1/31/2020	10362	903
2/1/2020	12581	1347

[az38]

@Anonymous 

syntax looks completely as mine

please, provide some details about data model. How does look like your data source?

[Cases] is column or measure?

Do you use any aggregation? try SUM() instead of MAX()?

[Anonymous]

@az38 Case is a column

I am new to power bi but I dont think i have done any aggregation. I have merged 3 different tables with data for deaths, recoveries and confirmed cases I then unpivoted the columns so they appeared in the status column below is what it looks like in query editor

[Anonymous]

Thank you that did the trick @az38 

[az38 · Accepted solution]

@Anonymous 

yes, data is aggregated in Visual, as I suggested.

try a measure

Today Cases =
CALCULATE(SUM('Covid-19 Tracker'[Cases]), ALLEXCEPT('Covid-19 Tracker', 'Covid-19 Tracker'[Date]) ) -
CALCULATE(SUM('Covid-19 Tracker'[Cases]), FILTER(ALL('Covid-19 Tracker'), 'Covid-19 Tracker'[Date] = MAX('Covid-19 Tracker'[Date]) -1) )