Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    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!

az38's avatar
az38
Community Champion
6 years ago

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) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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