Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
THENNA_41
Post Partisan
Post Partisan

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

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 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png

 

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

 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

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

1.png

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.