Forum Discussion
Converting Aggregated data to Daily
I am doing 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. To Identify the incremental increase in cases each day I created the meausure below . The measure works in some rows but in certain rows it does not work. for example the increase was not calculated as shown in the table below when cases increased from 59 - 60 or from 60 - 61 and there are many other instances when this occurs. Any one have thoughts on what is taking place here?
Anonymous try changing MAX to SUM, maybe there are two transactions on the same day
Today Cases = SUM('Daily Covid -19 Tracker'[Sum of total_cases]) - CALCULATE(SUM('Daily Covid -19 Tracker'[Sum of total_cases]), FILTER(ALL('Daily Covid -19 Tracker'), 'Daily Covid -19 Tracker'[date] = MAX('Daily Covid -19 Tracker'[date]) -1) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
3 Replies
Anonymous
why don't you use solution from here https://community.powerbi.com/t5/Desktop/Converting-daily-aggregated-COVID-data-to-an-accurate-Monthly/m-p/1130871#M515300 ?
the problem your data is more complicated, you have many different columns, use
Today Cases = CALCULATE(SUM('Daily Covid -19 Tracker'[Sum of total_cases]), ALLEXCEPT('Daily Covid -19 Tracker', 'Daily Covid -19 Tracker'[Date]) ) - CALCULATE(SUM('Daily Covid -19 Tracker'[Sum of total_cases]), FILTER(ALL('Daily Covid -19 Tracker'), 'Daily Covid -19 Tracker'[Date] = MAX('Daily Covid -19 Tracker'[Date]) -1) )az38 I used a different data source. and although your original post helped me with my original data set it responsed differently to the new data. Thank you for your help
Anonymous try changing MAX to SUM, maybe there are two transactions on the same day
Today Cases = SUM('Daily Covid -19 Tracker'[Sum of total_cases]) - CALCULATE(SUM('Daily Covid -19 Tracker'[Sum of total_cases]), FILTER(ALL('Daily Covid -19 Tracker'), 'Daily Covid -19 Tracker'[date] = MAX('Daily Covid -19 Tracker'[date]) -1) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
