Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ilikedata
New Member

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:

 

chart inquiry edited.JPG

 

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!

1 REPLY 1
amitchandak
Super User
Super User

@ilikedata ,

new column =
var _max = maxx(filter(Table, [Update Date] <earlier([Update Date])),[Update Date])
return
[Total Deaths] - sumx(filter(Table, [Update Date] =_max),[Total Deaths])

 

 

a measure with date table for last available date
Last Day Non Continuous = CALCULATE(sum([Total Deaths]),filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

 

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.