cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daxer12
Regular Visitor

append two tables using measure

Hi guys,

 

I have a table which looks like the following:

ProjectphasesstartDate (as a measure)endDate (as a measure)EstCost
Aa11/01/20201/08/2020100
Aa21/08/20201/08/2021200
Aa31/08/20211/11/2021300

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:

 

ProjectphasesstartDate (as a measure)endDate (as a measure)EstCostDynmicDate
Aa11/01/20201/08/2020100Jan-20
Aa11/01/20201/08/2020100Feb-20
Aa11/01/20201/08/2020100Mar-20
Aa11/01/20201/08/2020100Apr-20
Aa11/01/20201/08/2020100May-20
Aa11/01/20201/08/2020100Jun-20
Aa11/01/20201/08/2020100Jul-20
Aa21/08/20201/08/2021200Aug-20
Aa21/08/20201/08/2021200Sep-20
Aa21/08/20201/08/2021200Oct-20
Aa21/08/20201/08/2021200Nov-20
Aa21/08/20201/08/2021200Dec-20
Aa21/08/20201/08/2021200Jan-21
Aa21/08/20201/08/2021200Feb-21
Aa21/08/20211/08/2022200Mar-21
Aa21/08/20211/08/2022200Apr-21
Aa21/08/20211/08/2022200May-21
Aa21/08/20211/08/2022200Jun-21
Aa21/08/20211/08/2022200Jul-21
Aa31/08/20211/11/2021300Aug-21
Aa31/08/20211/11/2021300Sep-21
Aa31/08/20211/11/2021300Oct-21
Aa31/08/20211/11/2021300Nov-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

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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" )
)

 

1.png2.png

 

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
)

 

 

3.png

 

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.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

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" )
)

 

1.png2.png

 

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
)

 

 

3.png

 

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
Super User
Super User

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!