Calculating consumtion between 2 times

Question

I have some half hourly consumption data and I need to calculate the usage specifically between 02:30-05:00 am.

I have achieved this is excel with a sumifs statement but how to I convert this into a DAX?

Answer 1

Assuming you have a table with columns similar to those in the sample table below, I've included the DAX to calculate the sum of consumption between the times given. This also assumes that you want to calculate this sum for ALL days between 2:30 AM and 5:00 AM.

Sample Table (Table)

id	consumption	timestamp
1	4	2022-05-28 02:00
2	4	2022-05-28 02:30
3	5	2022-05-28 03:00
4	5	2022-05-28 03:30
5	6	2022-05-28 04:00
6	6	2022-05-28 04:30
7	5	2022-05-28 05:00
8	5	2022-05-28 05:30

Solution Measure

Consumption Sum = 
    CALCULATE(
        SUM('Table'[consumption]), 
        TIME(
            HOUR('Table'[timestamp]), 
            MINUTE('Table'[timestamp]), 
            SECOND('Table'[timestamp])
        ) >= TIMEVALUE("02:30:00"), 
        TIME(
            HOUR('Table'[timestamp]), 
            MINUTE('Table'[timestamp]), 
            SECOND('Table'[timestamp])
        ) <= TIMEVALUE("05:00:00")
    )

Sample Result

A similar result could be achieved using the SUMX function if that's more intuitive for you.