Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
LaurileeMarie
Regular Visitor

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

 

Sprint Schedule:

SprintStartEnd
1169/7/20229/20/2022
1179/21/202210/4/2022
11810/5/202210/18/2022
11910/19/202211/1/2022
12011/2/202211/15/2022
12111/16/202211/29/2022
12211/30/202212/13/2022
12312/14/202212/27/2022
12412/28/20221/10/2023

 

Capacity Schedule that we are connecting to in Power BI:

DateResourceCapacity
17-OctSara8
18-OctSara8
19-OctSara8
20-OctSara8
21-OctSara8
22-OctSara0
23-OctSara0
24-OctSara8
25-OctSara8
26-OctSara8
27-OctSara8
28-OctSara8
29-OctSara0
30-OctSara0
31-OctSara8
1-NovSara8
2-NovSara8
3-NovSara8
4-NovSara8
5-NovSara0
6-NovSara0
7-NovSara8
8-NovSara8
9-NovSara8
10-NovSara8
11-NovSara0
12-NovSara0
13-NovSara0

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
LaurileeMarie
Regular Visitor

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. 

 

LaurileeMarie_0-1666792016166.png

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
Not applicable

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

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

You are welcome Laurilee. Enjoy Dax 🙂

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.