Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a table which looks like the following:
Project | phases | startDate (as a measure) | endDate (as a measure) | EstCost |
A | a1 | 1/01/2020 | 1/08/2020 | 100 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 |
A | a3 | 1/08/2021 | 1/11/2021 | 300 |
In the above tables, Start and End dates are measures and they can be changed by the users based on their selection
How do I append a dynamic month/year in this table so the result looks something like this:
Project | phases | startDate (as a measure) | endDate (as a measure) | EstCost | DynmicDate |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | Jan-20 |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | Feb-20 |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | Mar-20 |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | Apr-20 |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | May-20 |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | Jun-20 |
A | a1 | 1/01/2020 | 1/08/2020 | 100 | Jul-20 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Aug-20 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Sep-20 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Oct-20 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Nov-20 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Dec-20 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Jan-21 |
A | a2 | 1/08/2020 | 1/08/2021 | 200 | Feb-21 |
A | a2 | 1/08/2021 | 1/08/2022 | 200 | Mar-21 |
A | a2 | 1/08/2021 | 1/08/2022 | 200 | Apr-21 |
A | a2 | 1/08/2021 | 1/08/2022 | 200 | May-21 |
A | a2 | 1/08/2021 | 1/08/2022 | 200 | Jun-21 |
A | a2 | 1/08/2021 | 1/08/2022 | 200 | Jul-21 |
A | a3 | 1/08/2021 | 1/11/2021 | 300 | Aug-21 |
A | a3 | 1/08/2021 | 1/11/2021 | 300 | Sep-21 |
A | a3 | 1/08/2021 | 1/11/2021 | 300 | Oct-21 |
A | a3 | 1/08/2021 | 1/11/2021 | 300 | Nov-21 |
I need help to expand the table with a dynamic date (which is a date range between start and end dates of phases on a monthly increment).
thanks
Daxer
Solved! Go to Solution.
Hi @Daxer12 ,
1.Create a calendar table. There is no relationship between two tables.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"MonthYear",
FORMAT ( [Date], "MMM" ) & "-"
& FORMAT ( [Date], "YY" ),
"Sort", FORMAT ( [Date], "YYYYMM" )
)
2.Create a measure and put it into Filters to filter table.
Measure =
IF (
MIN ( 'Calendar'[Date] ) >= 'Table'[startDate]
&& MIN ( 'Calendar'[Date] ) < [endDate],
1,
0
)
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Daxer12 ,
1.Create a calendar table. There is no relationship between two tables.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"MonthYear",
FORMAT ( [Date], "MMM" ) & "-"
& FORMAT ( [Date], "YY" ),
"Sort", FORMAT ( [Date], "YYYYMM" )
)
2.Create a measure and put it into Filters to filter table.
Measure =
IF (
MIN ( 'Calendar'[Date] ) >= 'Table'[startDate]
&& MIN ( 'Calendar'[Date] ) < [endDate],
1,
0
)
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Daxer12 , refer if this blog or file can help
File , with a different approach
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
@Daxer12 - So, Open Tickets does this essentially as part of it's calculation, you could just strip out the logic for it. But, basically it uses GENEARATE between the two dates. You would probably need to couple this with some additional logic. Might be better to do in Power Query. Is there a reasonable maximum involved here or how many rows are we dealing with in the base table?
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Thanks mate - there is no min or max limit.
I'll try your suggestion and really hoping it will work 🙂
Thanks heaps for your time again
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |