Looking for measure that aggregates values by day/week/month, then returns max aggregated value

[h4tt3n]

What I got:
Loads of timestamped data, one row per hour. 

I then aggregate the data in days, weeks, and months. The result is correct, but the scale to the left has very big numbers.

What I want:

I want a DAX measure that aggregates the data in either days, weeks, or months in a non -visible table, then returns the value of the largest column. In short, the max of a max.

Then I will use this value to find the correct factor with which to divide the sums, eg. a factor 1000 to get kWh from Wh.

Thanks in advance,

Cheers, Mike

[CoalesceIsMore]

I think something like this may work for you. 

First I created a measure for your Value

sumValue = SUM(Table1[Value])

Then used it in the calculation below.

The ISFILTERED serves to give you the granularity currently displayed.

maxValue2 = 
IF(ISFILTERED(Table1[TimeStamp].[Day]),
    CALCULATE(MAXX(VALUES(Table1[TimeStamp].[Day]), [sumValue]), ALL(Table1)),
    IF(ISFILTERED(Table1[TimeStamp].[Month]), 
        CALCULATE(MAXX(VALUES(Table1[TimeStamp].[Month]), [sumValue]), ALL(Table1)),
        IF(ISFILTERED(Table1[TimeStamp].[Quarter]),
            CALCULATE(MAXX(VALUES(Table1[TimeStamp].[Quarter]), [sumValue]), ALL(Table1))
        )     
    )
)

 This assumes you have a hierarchy on your TimeStamp. You can also replace Day with Date from that hierarchy.

[h4tt3n]

Thanks for the answer, I am looking into it. How would I orbganize my timestamp hierarchically, so I could use this syntax? 

Table1[TimeStamp].[Day]

Table1[TimeStamp].[Week]

Table1[TimeStamp].[Month]

 With the dot and the extra "column"?

Cheers, Mike

[CoalesceIsMore]

With fields that are date or datetime, it should happen automatically. When you import the table, just be sure to set the data type. [Select your column from the fields pane, go to the Modeling tab, change "Data type" to Date/Time] Then when you drag that field onto a visual like a Matrix, you'll see this in the rows section:

But it won't look much different in the Table / Field list before you do this, so you just have to know Power BI will treat those date/time fields like this, and give you the option for that dot notation.

If you have trouble with the hierarchy, you could always just create a date dimension, or add columns to your table to extract the date, month, quarter, year. Then you could access them directly without the dots.