Forum Discussion
Join tables with dates
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 !
8 Replies
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
FilteredTable4. 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.
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]))returnCALCULATE(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
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:
