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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors