Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Assigning a Sprint column based on date field

Good Afternoon! I am trying to assign a Sprint number to our capacity planning information within Power bi. Attached is an example of the worksheet we are pulling from and within Power BI, I wan...

Anonymous
3 years ago
Hi

Pleasease try this code

Sprint =
VAR __step1 =
    FILTER (
        SUMMARIZE ( Feuil1, Feuil1[Sprint], Feuil1[Start], Feuil1[End] ),
        Feuil2[Date] >= Feuil1[Start]
            && Feuil2[Date] <= Feuil1[End]
    )
RETURN
    MAXX ( __step1, Feuil1[Sprint] )

Feuil1 is your sprint schedule

Feuil 2 is your capacity schedule

Anonymous

Not applicable

3 years ago

Pleasease try this code

Sprint =
VAR __step1 =
    FILTER (
        SUMMARIZE ( Feuil1, Feuil1[Sprint], Feuil1[Start], Feuil1[End] ),
        Feuil2[Date] >= Feuil1[Start]
            && Feuil2[Date] <= Feuil1[End]
    )
RETURN
    MAXX ( __step1, Feuil1[Sprint] )

Feuil1 is your sprint schedule

Feuil 2 is your capacity schedule

Anonymous

Not applicable

3 years ago

THAT WORKED PERFECT!!! I realized i needed to be doing it in Power BI not Power Query, and I connected to the sprint calendar using power bi so that i was able to create the following : (For any newbies like me)

Sprint = VAR __step1 =

FILTER (

SUMMARIZE ( 'Sprint Calendar', 'Sprint Calendar'[Sprint], 'Sprint Calendar'[Start], 'Sprint Calendar'[End] ),

Capacity[Date] >='Sprint Calendar'[Start]

&& Capacity[Date] <= 'Sprint Calendar'[End]

)

RETURN

MAXX ( __step1, 'Sprint Calendar'[Sprint] )

'Sprint Calendar' is the name of my worksheet & 'Capacity' is the name of the secondary sheet, in case anyone else has the same issue.

Thank you James!!

Anonymous
Not applicable
3 years ago
You are welcome Laurilee. Enjoy Dax 🙂