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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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