I have a table that was originally produced in Excel and reflects the following data:
I created a measure that finds the monthly system peak value correctly but I need to return the values for each hour (1-24) on the date of the monthly peak in a matrix or table. The screen shot below shows what my matrix returns when I use the measure for values with hour ending for rows and month name for columns.
The measure for monthly peak is:
Monthly Peak Load = CALCULATE(MAX('System_Load'[Hr Sys Load]),FILTER('System_Load','System_Load'[Month]))
Screen shot of matrix:
The matrix shows (for example) the peak for all values of hour 1 in January which happens to be on January 1, 2019 (1108), however the overall max peak value for January occured on January 6 (1480) so I need to return the values from hour 1-24 on January 6, 2019. If the values from January 6 populated, hour 1 would be 1039 not 1108.
Any help is greatly appreciated!