Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        You are welcome Laurilee. Enjoy Dax 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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