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 want in excel - see picture below. But I'm stuck on how to create a measure in PowerBI. I do have a date table but I'm unclear if I should link up the date table to the start and end date?

 

Here is an example .pbix file: https://1drv.ms/u/s!AvCirXUmRp-JhcBx08tLAQrig-XM-A?e=XxukAt

 

 

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

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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