Join tables with dates

[Anonymous]

Hello,

I'm trying to join two tables but i'm not having the result expected.. could you help me ?

First Table :

Start month	End month
01/01/2021	31/01/2021
01/02/2021	28/02/2021
01/03/2021	31/03/2021

Second table : 

Start month	End month
01/01/2021	31/01/2021
01/02/2021	28/02/2021
01/03/2021	31/03/2021

I have date from 2016 for information.

I would like to create a board like below.

if i'm selecting a Start Month on first table,  i want to add end month after this date from the second table. Example :

Start month	End month
01/01/2021	31/01/2021
01/01/2021	28/02/2021
01/01/2021	31/03/2021

There is 2 filters :

1 for the Start Month (table 1) and the second one for End Month (Table 2) so i want to chose the period i want...

I'm not very clear but hope you can help me... (i'm french sorry for my english)

Thanks a lot !

[PaulDBrown]

I'm not actully too sure what you are trying to achive, but to get started you can try creating a new table using either:

1) In Power Query:

let
    Source = #"First Table",
    #"Removed Columns" = Table.RemoveColumns(Source,{"End month"}),
    Source1 = #"Second Table",
    #"Removed Columns1" = Table.RemoveColumns(Source1,{"Start month"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "End month", each #"Removed Columns1"),
    #"Expanded End Month" = Table.ExpandTableColumn(#"Added Custom", "End month", {"End month"}, {"End month.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded End Month",{{"End month.1", "End month"}})
    
in
    #"Renamed Columns"

2) or using DAX for a new table:

Combine dates = 
VAR StartD = VALUES('First Table'[Start month])
VAR EndD = VALUES('Second Table'[End month])
RETURN
CROSSJOIN(StartD, EndD)

Which gets you this

 Next create a visual with the fields from this new Combine Dates table, add a slicer for the Start Date from this table and you get:

[Anonymous]

Thank you a lot for your answer !! For now, Marik's solution just workfs perfectly so i'm going on his solution but i really appreciate !! Have a nice day 🙂

[Anonymous]

If I've got you right the solution may be this one (for PQ):

1. Create two tables: one with start dates, another with end dates;

2. Import them in Power Query (you will have two queries);

3. Modify code of second query (in the end you will get function, rename that function, for example as 'fnGetEndsOfMonths'):

(endOfMonth)=>
let
Origin = Excel.CurrentWorkbook(){[Name="end_month"]}[Content],
ChagedType = Table.TransformColumnTypes(Origin ,{{"End month", type date}}),
FilteredTable = Table.SelectRows(ChagedType , each [End month] > Date.From(endOfMonth))
in
FilteredTable

4. In first query (with start dates) add column via 'Invoke Custom Function'; in dialog box choose function from 3rd step and after that choose column with start dates.

5. At last expand tables in freshly created column.

[Anonymous]

Hello Marik,

Thanks a lot for you answer, really appreciate it !

The solution work for my indicators calculation but doesn't work when i want to put it on a board. For example if i'm selecting the period 01/07/2021 => 31/08/2021, my measure is OK, it give me the good number but when i'm adding this indicator 'per period' on a board adding a date, it doesn't (or don't ?....) work.

Here you can see my measure :

Nb per period =

VAR StartPeriode = CALCULATE(MAX('Time'[StartMonth]))

VAR EndPeriode = CALCULATE(MAX('Time'[called function.EndMonth]))

return

CALCULATE

(

DISTINCTCOUNT('ATable'[name]),

'ATable'[aDate] >= StartPeriode,

'ATable'[aDate] <= EndPeriode

)

On the board, i'm trying to put the 'ATable'[aDate] with the nb per period

Thanks again

[Anonymous · Accepted solution]

@Anonymous It isn't clear what does the measure have to calculate? What's your objective as a final result?

P.S. In the measure maybe it would be best to filter "ATable" firstly via FILTER and then to use filtered table as a filter for CALCULATE...

[Anonymous]

it works perfectly, thank you sir !

Teest =

VAR StartPeriode = CALCULATE(MAX('Time'[StartMonth]))

VAR EndPeriode = CALCULATE(MAX('Time'time_function.EndMonth]))

return

SUMX(

FILTER ('aTable',

AND('aTable'[aDate] >= StartPeriod, 'aTable'[aDate] <= EndPeriode)

)

,'aTable'[amounts]

)

[Anonymous]

I'm calculating a number of x per period.
My principal table (which i call previously 'aTable') have a date, a name, amounts (I obviously simplify), and so i want to calculate this number per period i chose (monthly periods) and the sum of the amounts per period.

On the detail (which is a board) i want to chose the same period and then put the date, and the amounts only on this period...

I don't know if it's more clear.. I will try your idea

[Anonymous]

Try this:

Nb per period =

VAR StartPeriode = VALUES('Time'[StartMonth])

VAR EndPeriode = VALUES('Time'[called function.EndMonth])

VAR FilteredTable=FILTER('ATable','ATable'[aDate] >= StartPeriode&&'ATable'[aDate] <= EndPeriode)

return

COUNTROWS(SUMMARIZE(FilteredTable,'ATable'[name])).

Keep in mind, variables StartPeriode and EndPeriode will work properly only when the corresponded fields are single-selected.