Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate column based on range in other table

Hey folks,

 

I'm looking for some assistance 🙂

 

I have a web form (response stored in SQL db) that team members complete on a particular date/time with the responses stored like so:

 

 

Teams run in various iterations/sprints (different table) (see below):

 

 

Rather than group the responses by date/month, we want them to be grouped by Sprint/iteration name (in the scenario above for example the responses would be for 'Sprint 10).

 

What would be the best way to go about it? No relationships between the two tables currently.

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi @Anonymous ,

    Try creating a column or measure as follows:

    column:

    Column =
MAXX (
    FILTER (
        'Sprint',
        Sprint[StartDate] <= Response[Date]
            && Sprint[EndDate] >= Response[Date]
    ),
    [IterationName]
)

    column.PNG

    measure:

    Measure =
MAXX (
    FILTER (
        'Sprint',
        Sprint[StartDate] <= MAX ( Response[Date] )
            && Sprint[EndDate] >= MAX ( Response[Date] )
    ),
    [IterationName]
)

    measure.PNG

    For more details, see the attached PBIX.

    Best regards

    Icey

    If this post helps,then please consider Accepting it as the solution to help the other members find it more quickly.

4 Replies