Calculate column based on range in other table

[Anonymous]

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.

[Icey · Accepted solution]

Hi @Anonymous ,

Try creating a column or measure as follows:

column:

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

measure:

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

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.

[Anonymous]

@Icey thanks - this (column) was the solution I was after - thanks to the others for a quick response too 🙂

[amitchandak]

I did not get it completely. But refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[Greg_Deckler]

I just created this recently:

https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/td-p/974201