Forum Discussion
Need Help in Dax for Measures
Hello Friends,
I am new to Power BI and looking for help in building 3 measures and having below data:
| Project | Sprint Name | Work Item | Title | Status | Type | Start Date | End Date |
| Project-A | Backlog | 1001 | Title-1 | New | User Story | 9/12/2022 12:00:00 AM | 9/24/2022 12:00:00 AM |
| Project-A | Backlog | 1002 | Title-2 | New | User Story | 9/12/2022 12:00:00 AM | 9/24/2022 12:00:00 AM |
| Project-A | Sprint 12 | 1003 | Title-3 | New | User Story | 6/5/2023 12:00:00 AM | 6/17/2023 12:00:00 AM |
| Project-A | Sprint 12 | 1004 | Title-4 | New | User Story | 6/5/2023 12:00:00 AM | 6/17/2023 12:00:00 AM |
| Project-A | Sprint 12 | 1005 | Title-5 | New | User Story | 6/5/2023 12:00:00 AM | 6/17/2023 12:00:00 AM |
| Project-A | Sprint 13 | 1006 | Title-6 | New | User Story | 6/19/2023 12:00:00 AM | 7/1/2023 12:00:00 AM |
| Project-A | Sprint 13 | 1007 | Title-7 | New | User Story | 6/19/2023 12:00:00 AM | 7/1/2023 12:00:00 AM |
| Project-A | Sprint 13 | 1008 | Title-8 | New | User Story | 6/19/2023 12:00:00 AM | 7/1/2023 12:00:00 AM |
| Project-A | Sprint 13 | 1009 | Title-9 | New | User Story | 6/19/2023 12:00:00 AM | 7/1/2023 12:00:00 AM |
| Project-B | Sprint 1002 | 1010 | Title-10 | New | User Story | 6/5/2023 12:00:00 AM | 6/17/2023 12:00:00 AM |
| Project-B | Sprint 1002 | 1011 | Title-11 | New | User Story | 6/5/2023 12:00:00 AM | 6/17/2023 12:00:00 AM |
| Project-B | Sprint 1003 | 1012 | Title-12 | New | User Story | 6/19/2023 12:00:00 AM | 7/1/2023 12:00:00 AM |
I need help with 3 Measures:
1) Measure to find the Start Date of the Sprint in the Project
Results:
Project-A, Backlog Sprint = 9/12/2022,
Project-A, Sprint 12 = 6/5/2023,
Project-A, Sprint 13 = 6/19/2023,
2) Measure to find the End Date of the Sprint in the Project
Results:
Project-A, Backlog Sprint = 9/24/2022,
Project-A, Sprint 12 = 6/17/2023,
Project-A, Sprint 13 = 7/1/2023,
3) Measure to Find the Current Sprint of the Project (Sprint range that falls in the current date = 6/19/2023)
Result:
Project-A = Sprint 12
Project-B = Sprint 1003
Thank you in advance for your help.
Prabhat
Hi,
before you start make sure your date fields are in a date format then try these:
1)Start of Sprint = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Sprint Name]))
2)End of Sprint = CALCULATE(MAX('Table'[End Date]),ALLEXCEPT('Table','Table'[Sprint Name]))
3) Current Sprint = if(and([Start of Sprint] <= TODAY(),
[End of Sprint] >= TODAY()),1,0)the first 2 will give you fields you can just add to a table:the 3rd measure will give you a 1 or 0, use this as a filter on a separate table visual to only display the records equal to 1:
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
1 Reply
Hi,
before you start make sure your date fields are in a date format then try these:
1)Start of Sprint = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Sprint Name]))
2)End of Sprint = CALCULATE(MAX('Table'[End Date]),ALLEXCEPT('Table','Table'[Sprint Name]))
3) Current Sprint = if(and([Start of Sprint] <= TODAY(),
[End of Sprint] >= TODAY()),1,0)the first 2 will give you fields you can just add to a table:the 3rd measure will give you a 1 or 0, use this as a filter on a separate table visual to only display the records equal to 1:
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍