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

Anonymous — Tue, 21 Nov 2023 06:41:16 GMT

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

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

tamerj1 — Tue, 21 Nov 2023 07:44:22 GMT

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

topic How to find Estimate (PAX PER 15 MINUTES) from Table A to Table B .. in DAX Commands and Tips

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

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