Forum Discussion

sam_rea_02's avatar
sam_rea_02
Frequent Visitor
3 years ago
Solved

Matching Date and time with another table

Hi Everyone,

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

Forecast Table

HOURHALF HOURQUARTER HOURSKILL
7:007:00ForecastShopI
7:007:30ForecastEmail
8:008:00ForecastShopI
8:008:30ForecastEmail
9:009:00ForecastPump
9:009:30ForecastProd
10:0010:00ForecastOverflow
10:0010:30ForecastFlier
11:0011:00ForecastShopI
11:0011:30ForecastEmail
12:0012:00ForecastPump
12:0012:30ForecastProd
13:0013:00ForecastOverflow
13:0013:30ForecastFlier
14:0014:00ForecastShopI
14:0014:30ForecastEmail
15:0015:00ForecastPump
15:0015:30ForecastProd
16:0016:00ForecastOverflow
16:0016:30ForecastFlier
17:0017:00ForecastShopI
17:0017:30ForecastEmail
18:0018:00ForecastPump
18:0018:30ForecastProd
19:0019:00ForecastOverflow
19:0019:30ForecastFlier
20:0020:00ForecastFlier
20:0020:30ForecastFlier

 

Raw Table

DATEHOURHALF HOURQUARTER HOURSKILL
07/06/202310:0010:3010:45Pump
07/06/20238:008:008:00Prod
07/06/20238:008:008:15Prod
07/06/20238:008:308:30Flier
07/06/202312:0012:3012:45Overflow

 

Fcast Table

Time 6/7/236/8/23
8:00 AM75
8:30 AM1311
9:00 AM1614
9:30 AM1712
10:00 AM1816
10:30 AM1515
11:00 AM2019
11:30 AM2016
12:00 PM1212
12:30 PM1112
1:00 PM1611
1:30 PM1514
2:00 PM1512
2:30 PM1417
3:00 PM1713
3:30 PM1919
4:00 PM1312
4:30 PM1413
5:00 PM129
5:30 PM76
6:00 PM74
6:30 PM33
7:00 PM21
7:30 PM11

 

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.

 

DATEHOURHALF HOURQUARTER HOURSKILLForecast
07/06/20237:007:30ForecastShopI0
07/06/20238:008:00ForecastEmail7
07/06/20238:008:30ForecastShopI13
07/06/20239:009:00ForecastEmail16
07/06/20239:009:30ForecastPump17
07/06/202310:0010:00ForecastProd18
07/06/202310:0010:30ForecastOverflow15
07/06/202311:0011:00ForecastFlier20
07/06/202311:0011:30ForecastShopI20
07/06/202312:0012:00ForecastEmail12
07/06/202312:0012:30ForecastPump11
07/06/202313:0013:00ForecastProd16
07/06/202313:0013:30ForecastOverflow15
07/06/202314:0014:00ForecastFlier15
07/06/202314:0014:30ForecastShopI14
07/06/202315:0015:00ForecastEmail17
07/06/202315:0015:30ForecastPump19
07/06/202316:0016:00ForecastProd13
07/06/202316:0016:30ForecastOverflow14
07/06/202317:0017:00ForecastFlier12
07/06/202317:0017:30ForecastShopI7
07/06/202318:0018:00ForecastEmail7
07/06/202318:0018:30ForecastPump3
07/06/202319:0019:00ForecastProd2
07/06/202319:0019:30ForecastOverflow1
07/06/202320:0020:00ForecastFlier0
07/06/202320:0020:30ForecastFlier0
07/06/202310:0010:3010:45Pump0
07/06/20238:008:008:00Prod0
07/06/20238:008:008:15Prod0
07/06/20238:008:308:30Flier0
07/06/202312:0012:3012:45Overflow0

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • sam_rea_02's avatar
    sam_rea_02
    Frequent Visitor
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        sam_rea_02
        Frequent Visitor
        3 years ago

        Hi Anonymous ,

         

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