Combine data that is valid for a period with data that is specific to a certain time

[henrika]

Hi,

I have a model where the main table has a monthly record per unit with several datacolumns like sketched below.

Date	Unit	Data1	Data2	Data3	DataN
01.11.2018	A	1	4	7	10
01.12.2018	A	2	5	8	11
01.11.2018	B	3	6	9	12

Then there is a table which has some factors that is valid for a time period for the different units (e.g. as below).
What would be the best way to combine this so that I can iterate over the data in the main table and apply the factors of the other table?

The factortable could be expanded to months and they could be connected with a helpercolumn that consists of e.g. "Unit-Date", but that seems a bit excessive.
(e.g. TestCalc = SUMX(DataTable, DataTable[Data1] * RELATED(FactorTable[Factor_X])

StartTime	EndTime	Unit	Factor_X	Factor_Y	Factor_Z
	31.12.2017	A	1	11	110
01.01.2018	31.12.2020	A	1	10	100
01.01.2021		A	2	10	102
01.01.2019	31.12.2021	B	3	12	110
01.01.2022		B	1	11	105

Thanks in advance for your consideration!

[Anonymous]

Hi @henrika,

I'd like to suggest you to unpivot column on these date fields to convert to attribute and values, then you can simply write Dax expression and condition to filter on these unpivot date values.

Unpivot columns - Power Query | Microsoft Learn

Regards,

Xiaoxin Sheng

[henrika]

Hi @Anonymous and thanks for your reply!

Wouldn't that give me many rows from that DAX filtering if I should unpivot on the date columns? 
What about linking on "unit" and using your datefiltering like StartDate <= date && EndDate >= date. That could maybe get us a unique row. 

I won't have time to test today, but will test both ideas soon-ish.
Regards,
Henrik

[Anonymous · Accepted solution]

Hi @henrika,

It sounds like you want to analysis with date ranges that define by multiple fields. for this scenario, I'd like to suggest you look at the following blog 'start, end date' parts if help:

Before You Post, Read This: start, end date  

Regards,

Xiaoxin Sheng