Forum Discussion

Anonymous's avatar
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's avatar
    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's avatar
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's avatar
Anonymous
Not applicable
3 years ago

You are welcome Laurilee. Enjoy Dax 🙂