Matching Date and time with another table

[sam_rea_02]

Hi Everyone,

Need your help on how to put it on power bi. I have 3 tables

Forecast Table

HOUR	HALF HOUR	QUARTER HOUR	SKILL
7:00	7:00	Forecast	ShopI
7:00	7:30	Forecast	Email
8:00	8:00	Forecast	ShopI
8:00	8:30	Forecast	Email
9:00	9:00	Forecast	Pump
9:00	9:30	Forecast	Prod
10:00	10:00	Forecast	Overflow
10:00	10:30	Forecast	Flier
11:00	11:00	Forecast	ShopI
11:00	11:30	Forecast	Email
12:00	12:00	Forecast	Pump
12:00	12:30	Forecast	Prod
13:00	13:00	Forecast	Overflow
13:00	13:30	Forecast	Flier
14:00	14:00	Forecast	ShopI
14:00	14:30	Forecast	Email
15:00	15:00	Forecast	Pump
15:00	15:30	Forecast	Prod
16:00	16:00	Forecast	Overflow
16:00	16:30	Forecast	Flier
17:00	17:00	Forecast	ShopI
17:00	17:30	Forecast	Email
18:00	18:00	Forecast	Pump
18:00	18:30	Forecast	Prod
19:00	19:00	Forecast	Overflow
19:00	19:30	Forecast	Flier
20:00	20:00	Forecast	Flier
20:00	20:30	Forecast	Flier

Raw Table

DATE	HOUR	HALF HOUR	QUARTER HOUR	SKILL
07/06/2023	10:00	10:30	10:45	Pump
07/06/2023	8:00	8:00	8:00	Prod
07/06/2023	8:00	8:00	8:15	Prod
07/06/2023	8:00	8:30	8:30	Flier
07/06/2023	12:00	12:30	12:45	Overflow

Fcast Table

Time	6/7/23	6/8/23
8:00 AM	7	5
8:30 AM	13	11
9:00 AM	16	14
9:30 AM	17	12
10:00 AM	18	16
10:30 AM	15	15
11:00 AM	20	19
11:30 AM	20	16
12:00 PM	12	12
12:30 PM	11	12
1:00 PM	16	11
1:30 PM	15	14
2:00 PM	15	12
2:30 PM	14	17
3:00 PM	17	13
3:30 PM	19	19
4:00 PM	13	12
4:30 PM	14	13
5:00 PM	12	9
5:30 PM	7	6
6:00 PM	7	4
6:30 PM	3	3
7:00 PM	2	1
7:30 PM	1	1

I need to get the date from raw table. after that get all the data from forecast table. Need to compare the date from Fcast table, once the date is the same with Fcast table, the value for the fcast should be put in proper time and also the value. The output for comparing this will be in another column as Forecast. the raw table will be inserted below but then the forecast value is 0 since the quater hour has no forecast word. Below is the output.

DATE	HOUR	HALF HOUR	QUARTER HOUR	SKILL	Forecast
07/06/2023	7:00	7:30	Forecast	ShopI	0
07/06/2023	8:00	8:00	Forecast	Email	7
07/06/2023	8:00	8:30	Forecast	ShopI	13
07/06/2023	9:00	9:00	Forecast	Email	16
07/06/2023	9:00	9:30	Forecast	Pump	17
07/06/2023	10:00	10:00	Forecast	Prod	18
07/06/2023	10:00	10:30	Forecast	Overflow	15
07/06/2023	11:00	11:00	Forecast	Flier	20
07/06/2023	11:00	11:30	Forecast	ShopI	20
07/06/2023	12:00	12:00	Forecast	Email	12
07/06/2023	12:00	12:30	Forecast	Pump	11
07/06/2023	13:00	13:00	Forecast	Prod	16
07/06/2023	13:00	13:30	Forecast	Overflow	15
07/06/2023	14:00	14:00	Forecast	Flier	15
07/06/2023	14:00	14:30	Forecast	ShopI	14
07/06/2023	15:00	15:00	Forecast	Email	17
07/06/2023	15:00	15:30	Forecast	Pump	19
07/06/2023	16:00	16:00	Forecast	Prod	13
07/06/2023	16:00	16:30	Forecast	Overflow	14
07/06/2023	17:00	17:00	Forecast	Flier	12
07/06/2023	17:00	17:30	Forecast	ShopI	7
07/06/2023	18:00	18:00	Forecast	Email	7
07/06/2023	18:00	18:30	Forecast	Pump	3
07/06/2023	19:00	19:00	Forecast	Prod	2
07/06/2023	19:00	19:30	Forecast	Overflow	1
07/06/2023	20:00	20:00	Forecast	Flier	0
07/06/2023	20:00	20:30	Forecast	Flier	0
07/06/2023	10:00	10:30	10:45	Pump	0
07/06/2023	8:00	8:00	8:00	Prod	0
07/06/2023	8:00	8:00	8:15	Prod	0
07/06/2023	8:00	8:30	8:30	Flier	0
07/06/2023	12:00	12:30	12:45	Overflow	0

Hope you can help me since I am really stuck on comparing it because of the date and time. Thanks in advance

[Anonymous · Accepted solution]

Hi @sam_rea_02 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Unpivot the date columns of query "Fcast"

2. Create a calculated table as below

Table = UNION(DISTINCT('Forecast'),SUMMARIZE('Raw','Raw'[HOUR],'Raw'[HALF HOUR],'Raw'[QUARTER HOUR],Raw[SKILL]))

3. Create a measure as below

Forecast = 
VAR _halfhour =
    SELECTEDVALUE ( 'Table'[HALF HOUR] )
VAR _date =
    SELECTEDVALUE ( 'Raw'[DATE] )
RETURN
    CALCULATE (
        SUM ( 'Fcast'[Value] ),
        FILTER ( 'Fcast', 'Fcast'[Date] = _date && 'Fcast'[Time] = _halfhour ),
        FILTER ( 'Raw', 'Raw'[DATE] = _date && 'Raw'[HALF HOUR] = _halfhour )
    ) + 0

4. Create a table visual

Best Regards

[sam_rea_02]

Hope some on can help me on this since I am stuck on how I can solve this. Please help

[Anonymous · Accepted solution]

Hi @sam_rea_02 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Unpivot the date columns of query "Fcast"

2. Create a calculated table as below

Table = UNION(DISTINCT('Forecast'),SUMMARIZE('Raw','Raw'[HOUR],'Raw'[HALF HOUR],'Raw'[QUARTER HOUR],Raw[SKILL]))

3. Create a measure as below

Forecast = 
VAR _halfhour =
    SELECTEDVALUE ( 'Table'[HALF HOUR] )
VAR _date =
    SELECTEDVALUE ( 'Raw'[DATE] )
RETURN
    CALCULATE (
        SUM ( 'Fcast'[Value] ),
        FILTER ( 'Fcast', 'Fcast'[Date] = _date && 'Fcast'[Time] = _halfhour ),
        FILTER ( 'Raw', 'Raw'[DATE] = _date && 'Raw'[HALF HOUR] = _halfhour )
    ) + 0

4. Create a table visual

Best Regards

[sam_rea_02]

Hi @Anonymous ,

I havent seen the pbix? will it be possible to attached again. thanks

[Anonymous · Accepted solution]

Hi @sam_rea_02 ,

I forgot to attach it, sorry for that. Please find it in the attachment.

Best Regards