How to find Estimate (PAX PER 15 MINUTES) from Table A to Table B ..

[Anonymous]

I want fetch value from Table A to Table B for ESTIMATE PER 15 MINUTES. I have attached Sample Data for reference . also attached Excel calcautlions for reference as  well

Below Excel formula used for calculations

=SUMIFS(AY:AY,AV:AV,">="&$BQ28,AV:AV,"<"&$BQ29)

AY - STD , AV - ESTIMATE , BQ Column - TIME (Table B)

TABLE A :

AV        AW          AX               AY                                           --- Excel column 

STD    FLIGH   T AIRPORT     ESTIMATE
06:00  QF402      SYD            142
06:05  QF765      PER            145
06:45   QF673    ADL            CX
07:00   QF410    SYD          132
07:05   QF604    BNE          126
08:00   QF418   SYD           148
08:05   QF1514  CBR            83
08:25  QF1551  HBA          109
08:30 QF2051   DPO         36

TABLE B :

BQ                BR                                           -- Excel Column 

TIME-           PAX  ( Output column in EXCEL)

03:00
03:15
03:30
03:45
04:00
04:15
04:30
04:45
05:00
05:15
05:30
05:45
06:00    287 -- 142+145 6 to 6.15
06:15
06:30
06:45
07:00     258 -- 132+126 7 to 7.15
07:15
07:30
07:45
08:00    231 -- 148+83 8 to 8.15
08:15
08:30    145 --109+36 8.15 to 8.30

Converting this  data in to power bi .  How to convert this formula in Dax ? looking for support .. thanks in advance 

[tamerj1 · Accepted solution]

Hi @Anonymous 
What is "CX" in 'TABLE A'[ESTIMATE]?

PAX = 
VAR CurrentTime = 'TABLE B'[TIME]
VAR TimesAfter = FILTER ( VALUES ( 'TABLE B'[TIME] ), 'TABLE B'[TIME] > CurrentTime )
VAR NextTime = MINX ( TimesAfter, 'TABLE B'[TIME] )
VAR FiteredTable = FILTER ( 'TABLE A', 'TABLE A'[STD] >= CurrentTime && 'TABLE A'[STD] < NextTime )
VAR Result =
    SUMX ( 
        FiteredTable,
        IFERROR ( VALUE ( 'TABLE A'[ESTIMATE] ), BLANK ( ) )
    )
RETURN
    Result