Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
Sprint | Start | End |
116 | 9/7/2022 | 9/20/2022 |
117 | 9/21/2022 | 10/4/2022 |
118 | 10/5/2022 | 10/18/2022 |
119 | 10/19/2022 | 11/1/2022 |
120 | 11/2/2022 | 11/15/2022 |
121 | 11/16/2022 | 11/29/2022 |
122 | 11/30/2022 | 12/13/2022 |
123 | 12/14/2022 | 12/27/2022 |
124 | 12/28/2022 | 1/10/2023 |
Capacity Schedule that we are connecting to in Power BI:
Date | Resource | Capacity |
17-Oct | Sara | 8 |
18-Oct | Sara | 8 |
19-Oct | Sara | 8 |
20-Oct | Sara | 8 |
21-Oct | Sara | 8 |
22-Oct | Sara | 0 |
23-Oct | Sara | 0 |
24-Oct | Sara | 8 |
25-Oct | Sara | 8 |
26-Oct | Sara | 8 |
27-Oct | Sara | 8 |
28-Oct | Sara | 8 |
29-Oct | Sara | 0 |
30-Oct | Sara | 0 |
31-Oct | Sara | 8 |
1-Nov | Sara | 8 |
2-Nov | Sara | 8 |
3-Nov | Sara | 8 |
4-Nov | Sara | 8 |
5-Nov | Sara | 0 |
6-Nov | Sara | 0 |
7-Nov | Sara | 8 |
8-Nov | Sara | 8 |
9-Nov | Sara | 8 |
10-Nov | Sara | 8 |
11-Nov | Sara | 0 |
12-Nov | Sara | 0 |
13-Nov | Sara | 0 |
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
Solved! Go to Solution.
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
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
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)
You are welcome Laurilee. Enjoy Dax 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
20 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
27 | |
27 | |
13 | |
12 | |
12 |