Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Time intelligence for hours

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 us...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      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.