How to calculate the difference between two measures based on a Date Column

[JSIQUEI-YYC-ENB]

hello everyone, 

I have a table with the following columns and measure: 

I would need a measure (New Measure) to calculate the difference in Budget between the last available Report Date (9/30/2023) and the previous date right before (09/15/2023). The desired result is shown in the gree column above...any help from the experts will be really apprecited! thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Report date column to the Date column of the Calendar Table.  To your visual, drag Date column from the Calendar Table.  Assuming Budget is a measure that you have already written, write this measure

Measure = [Budget]-calculate([budget],calculatetable(lastnonblank(Calendar[Date],calculate([budget])),datesbetween(calendar[date],minx(all(calendar[Date]),calendar[date]),min(calendar[date])-1)))

If this does not help, then share the download link of the PBI file.