I have half-hourly consumption data and I need to calculate the sum of consumption that takes place between 2:30 AM and 5:00 AM.
I have achieved this in Excel with a SUMIF statement. How do I do this with DAX, though?
1 Answers
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.
Related