Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure that sums values between two different date fields - pbix example attached

I have a table with three fields: start date, end date, and a value. I want a measure that will sum the value if the date is between the start and end date of the row.   I can create the visual I w...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is one way to do it, with no relationship between the tables.

     
    NewMeasure =
    VAR thisdate =
        MIN ( 'Date Table'[Date] )
    RETURN
        CALCULATE (
            SUM ( 'Data Table'[Value] ),
            'Data Table'[Start Date] <= thisdate,
            'Data Table'[End Date] >= thisdate
        )
     
    Regards,
    Pat
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Here is one way to do it, with no relationship between the tables.

 
NewMeasure =
VAR thisdate =
    MIN ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Data Table'[Value] ),
        'Data Table'[Start Date] <= thisdate,
        'Data Table'[End Date] >= thisdate
    )
 
Regards,
Pat