Forum Discussion
Helper IHow to calculate the difference between two measures based on a Date Column
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!
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.
1 Reply
- Ashish_Mathur
Super User
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.