Excel Formula based on time convert to DAX

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.