Forum Discussion

Helper I

6 years ago

Solved

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/0...

Need Help

Anonymous

5 years ago

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.

Greg_Deckler

Community Champion

6 years ago

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