Forum Discussion
append two tables using measure
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
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.
4 Replies
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
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
