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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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