append two tables using measure

[Daxer12]

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

[v-stephen-msft · Accepted 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.

[amitchandak]

@Daxer12 , refer if this blog or file can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

File , with a different approach

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Greg_Deckler]

@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

[Daxer12]

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