Time intelligence for hours

[Anonymous]

Here is my problem:

I have hourly data that can be graphed per hour and perform operations such as forecasting.

However, When trying to do a cumulative sum per month of the energy used (time intelligence), I can only get it in the resolution of days, not hours. Clarifying, the output is more blocky since the cumulative output is the sum per day; I would like output to represent sum per hour which would be more jagged like the example above.

My data looks like this. (for 10 days)

This is my cumulative sum calculation and it successfully accumulates the energy per month. Note StartTime is what is used for the date input.

// [Sum of Energy = Sum(data)].

The problem happens when creating a visual. 

I am able to get a working cumulative sum visual when using [Dated] as my axis (see second photo). The resolution is days which is expected.

HOWEVER, when using [StartTime] as my axis, my cumulative sum calculation breaks and my visual is my data shown per hour, not cumulative summed per month:

Any suggestions are much appreciated!

[Greg_Deckler]

Can you post your data in a format that can be copied and pasted easily instead of images? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, I am working on some Quick Measures for "Time Intelligence the Hard Way". Here is an example of a calculation for Total Year To Date. You should be able to use the same basic technique.

TotalYTDHW = 
VAR __MaxYear = MAX('Years'[Year])
VAR __MaxMonth = MAX('Months'[MonthSort])
VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month]))
RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] <= __MaxMonth),[Value])

[Greg_Deckler]

I just posted the Quick Measures here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Holy, thanks for diving into this topic this thoroughly. 

Can you help me implement a MTD with the hour resolution though? I'm new to DAX and find the syntax a bit difficult.

From your code,

TITHW_TotalYTDHW =
VAR __MaxYear = MAX('Years'[Year]) //change to max month?
VAR __MaxMonth = MAX('Months'[MonthSort]) //change to max day?
VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month])) //why reference this table?
RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] <= __MaxMonth),[Value])

Currently I have my full date and time in PiEnergy[StartTime], and my data in PiEnergy[EnergyConsumed].

I understand I should make columns of the month and day and hour numerically, ie (jan =1)

For clarity, can you reply with a TITHW_TotalMTDHW formula and how to create 'TheHardWay' or reference my own table to implement this DAX?

Thanks for your patience.

[Greg_Deckler · Accepted solution]

Hmm, perhaps something like the following, also see attachment.

I started with a single table PiEnergy with [StartTime] and [EnergyConsumed] columns.

I created the following calculated columns (you could use ADDCOLUMNS to do this within the measure but that doesn't seem necessary and this is more efficient.

Month = MONTH([StartTime])

Day = DAY([StartTime])

Hour = HOUR([StartTime])

The measure is very similar to the technique mentioned earlier with the wrinkle of the time component, requiring two separate calculations:

TITHW_TotalMTDHW = 
VAR __curMonth = MAX(PiEnergy[Month])
VAR __curDay = MAX(PiEnergy[Day])
VAR __curHour = MAX(PiEnergy[Hour])
VAR __tmpTable = CALCULATETABLE(PiEnergy,ALL(PiEnergy[StartTime]))
VAR __previousDaysConsumption = SUMX(FILTER(__tmpTable,[Month] = __curMonth && [Day] < __curDay),[EnergyConsumed])
VAR __currentDayConsumption = SUMX(FILTER(__tmpTable,[Month] = __curMonth && [Day] = __curDay && [Hour] <= __curHour),[EnergyConsumed])  
RETURN __previousDaysConsumption + __currentDayConsumption

Let me know if this works or needs tweaking.