Help Please!: Calculate Difference from Previous Date (Dates Are Missing)
Hi Everyone, Appreciate joining the community as a novice PBI user and any help that could be provided. I have been trying to calculate the difference for rows in a table regarding cumulative death data in descending order related to a certain condition for multiple locations (called "a", "b", "c", "d", and "e" for this inquiry). Unfortunately, dates are skipped, so that only Sunday, Monday, Tuesday, Wednesday, and Thursday only have data (in most cases)--this is not always consistent. I've tried doing this many, many ways via a Calculated Column (i.e., "Total Deaths Difference (from Previous Report) Calc Column") versus Measure (i.e., "Total Deaths Difference (from Previous Report) Measure") to get the difference, but for the Measure method it gets caught up in the jump from Thursday to Sunday data (as there is no consecutive dates in between) and provides the MAX value and not a difference using Thursday's data (look at Sunday, May 2). For the Calculated Column and although you cannot see it, it keeps using the MAX value, which with this data, sometimes rolls-back to a lower value or stays the same and the difference result comes out incorrect as a result of this. Here are the DAX formulas that I am referring to: Measure: Measure = CALCULATE(MAX('Table'[Total Deaths])) - CALCULATE(MAX('Table'[Total Deaths]), PREVIOUSDAY('Table'[Update Date])) Calculated Column: Calculated Column = VAR _dates = 'Table'[Update Date] VAR _PrevRow = CALCULATE( MAX('Table'[Total Deaths]), FILTER('Table', 'Table'[Update Date]<_dates)) return [Total Deaths]- _PrevRow Here is a copy of the data table via a Matrix that I made: I added an Index Column just for my reference and in an another attempt to process the data, so you can ignore this as I want to keep this simple as there must be a one-step way to fix my current DAX to adjust for the missing dates! I'm assuming some sort of FILTER needs to be applied to tell the software to skip Friday and Sunday or at least a logic function telling it that if a date doesn't exist to keep going back until it finds one that does. Curbal had something on this in a video, but it just didn't address the potential for interrupted/missing dates and was under the presumption that the MAX value was always the most recent/newest cumulative total, which is not always the case as data sometimes gets rolled back as deaths might be reclassified/removed. Here's the link if this is helpful: https://www.youtube.com/watch?v=mLZ4KISPcZ8 Any input would be appreciated. Let me know if I need to clarify something. Thank you!