Assigning a Sprint column based on date field

[LaurileeMarie]

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 want to be able to say that if it is between these dates, it needs to return the associated sprint. 

Sprint Schedule:

Sprint	Start	End
116	9/7/2022	9/20/2022
117	9/21/2022	10/4/2022
118	10/5/2022	10/18/2022
119	10/19/2022	11/1/2022
120	11/2/2022	11/15/2022
121	11/16/2022	11/29/2022
122	11/30/2022	12/13/2022
123	12/14/2022	12/27/2022
124	12/28/2022	1/10/2023

Capacity Schedule that we are connecting to in Power BI:

Date	Resource	Capacity
17-Oct	Sara	8
18-Oct	Sara	8
19-Oct	Sara	8
20-Oct	Sara	8
21-Oct	Sara	8
22-Oct	Sara	0
23-Oct	Sara	0
24-Oct	Sara	8
25-Oct	Sara	8
26-Oct	Sara	8
27-Oct	Sara	8
28-Oct	Sara	8
29-Oct	Sara	0
30-Oct	Sara	0
31-Oct	Sara	8
1-Nov	Sara	8
2-Nov	Sara	8
3-Nov	Sara	8
4-Nov	Sara	8
5-Nov	Sara	0
6-Nov	Sara	0
7-Nov	Sara	8
8-Nov	Sara	8
9-Nov	Sara	8
10-Nov	Sara	8
11-Nov	Sara	0
12-Nov	Sara	0
13-Nov	Sara	0

I have tried Nested If statments with little luck, and a switch (True() Statement that i found in another post but neither worked. Any help at all would be greatly appreciated!! 

Thanks so much in advance!

laurilee

[LaurileeMarie]

Thank you James! I am a very new user and not incredibly technical. I input what i think you were getting at and it is giving me an error. 

Any thoughts? Also my assumption is that i would continue to define out the sprints and their numbers before the ) Return. Is that correct? Thank you so much for your help!

laurilee

[Anonymous · Accepted solution]

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

[LaurileeMarie]

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]

You are welcome Laurilee. Enjoy Dax 🙂