Forum Discussion

JSIQUEI-YYC-ENB's avatar
JSIQUEI-YYC-ENB
Icon for Helper I rankHelper I
3 years ago
Solved

How 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/3...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

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.