Excel Formula based on time convert to DAX

[Anonymous]

I want to convert  below Excel fomulas  to DAX. i have mentioned Excel column headers and AM & PM model static values,

 BQ       BR    --- EXCEL COLUMN HEADER 

TIME    T1

 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

06:15

06:30

06:45

07:00    258

07:15

07:30

07:45

08:00   231

08:15   109

08:30   219

AM MODEL:

                                                                     CO    -- FACTOR EXCEL COLUMN 

PRESENTATION ESTIMATE DOMESTIC AM MODEL

                                                START TIME:00:01:00

                                                FINISH TIME:08:45:00

ALPHA    BETA     X    BEFORE    WEIBULL   FACTOR

3.5           1.2     0.25     2.25         0.0041       0%

                         0.50     2.00          0.0456      4%

                          0.75   1.75          0.1755     13%

                          1.0  0 1.50          0.4104      23%

                          1.25 1.25            0.6845     27%

                         1.50  1.00            0.8874      20%

                         1.75  0.75           0.9764       9%

                         2.00  0.50          0.9975        2%

PM MODEL:

                                                                      CV    -- FACTOR EXCEL COLUMN 

PRESENTATION ESTIMATE DOMESTIC PM MODEL

                                                   START TIME:09:00:00

                                                  FINISH TIME:23:59:00 PM

ALPHA   BETA   X    BEFORE   WEIBULL    FACTOR

3.7          2.3   0.25  3.50          0.0003          0%

                      0.50  3.25         0.0035           0%

                      0.75 3.00           0.0157         1%

                     1.00  2.75          0.0448          3%

                     1.25 2.50           0.0995         5%

                    1.50 2.25            0.1859        9%

                    1.75 2.00          0.3050         12%

                    2.00 1.7          5 0.4491        14%

                   2.25 1.50           0.6022         15%

                  2.50 1.25             0.7437       14%

                  2.75 1.00           0.8559         11%

                3.00  0.75           0.9309           8%

                 3.25 0.50          0.9725            7%

EXCEL OUTPUT :

USING FORMULA : FOR TIME 

  START TIME:00:01:00

  FINISH TIME:08:45:00

=(BR21*$CO$11)+(BR20*$CO$12)+(BR19*$CO$13)+(BR18*$CO$14)+(BR17*$CO$15)+(BR16*$CO$16)+(BR15*$CO$17)+(BR14*$CO$18)

         START TIME:09:00:00

         FINISH TIME:23:59:00 PM

=(BR66*$CV$11)+(BR65*$CV$12)+(BR64*$CV$13)+(BR63*$CV$14)+(BR62*$CV$15)+(BR61*$CV$16)+(BR60*$CV$17)+(BR59*$CV$18)+(BR58*$CV$19)+(BR57*$CV$20)+(BR56*$CV$21)+(BR55*$CV$22)+(BR54*$CV$23)

EXCEL OUTPUT :

TIME    T1

02:00

02:15

02:30

02:45

03:00

03:15

03:30

03:45    1

04:00    12

04:15     37

04:30     67

04:45    80

05:00    69

05:15    59

05:30    67

05:45    72

06:00   62

06:15   58

06:30   84

06:45   121

07:00   148

07:15   164

07:30   183

07:45   208

08:00   227

08:15   225

08:30   208

how to convert this formula  to dax  for  time based withAM and PM static value ? looking for support .. thanks in advance 

[123abc]

To convert the Excel formula to a DAX formula, you can use the following approach in Power BI or other tools that support DAX:

1. Create a Time table: Create a table that contains a column with time values. You can use the "CALENDAR" function in DAX to generate a table with time values.

TimeTable = CALENDAR(TIME(0, 1, 0), TIME(23, 59, 0), 15)

Calculate the Factor for AM and PM models based on the time:

FactorAM =
SUMX(
TimeTable,
SWITCH(
TRUE(),
TimeTable[Time] < TIME(0, 1, 0), 0,
TimeTable[Time] < TIME(6, 0, 0),
TimeTable[Time] * VALUES('AM Model'[Factor]),
TimeTable[Time] >= TIME(6, 0, 0), VALUES('AM Model'[Factor])
)
)

FactorPM =
SUMX(
TimeTable,
SWITCH(
TRUE(),
TimeTable[Time] < TIME(9, 0, 0), 0,
TimeTable[Time] >= TIME(9, 0, 0), VALUES('PM Model'[Factor])
)
)

Note: Replace 'AM Model' and 'PM Model' with the actual names of your tables containing AM and PM model factors.

1. Calculate the output using the calculated factors:

Output =
SUMX(
TimeTable,
TimeTable[FactorAM] * [T1] +
TimeTable[FactorPM] * [T1]
)

In this example, I assumed that your input table has a column named 'T1' containing the values for each time. Adjust the column and table names as needed based on your actual data structure.

This DAX formula should give you the output based on the factors and time values for both the AM and PM models.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.