1

I'm working on having a column whose values are the running MAX of another column.

My main table has three columns and I use the summarize function to virtually add another column named as SUM OF EACH DATETIME to the summarized table. Now I want to have the running MAX of the SUM OF EACH DATETIME column in the summarized table in another new column as MAX of Sum column. My table and its preferred columns are shown below:

enter image description here

I'd appreciate it if you kindly guide me how can I have the MAX of Sum column in my summarized table. I should note that the formula to calculate the SUM OF EACH DATETIME column is:

SUMMARIZE(TABLE, TABLE[DateTimeStamp],
 "SUM OF EACH DATETIME", IF(COUNTROWS(TABLE)=calculate(DISTINCTCOUNT(TABLE[Name]), ALLSELECTED()),SUM(TABLE[Value]),BLANK()))
Parham.Trader
  • 47
  • 1
  • 4
  • Hi @Parham, your column "sum of each date time" is a physical column? or you created that using DAY (virtuar)? – mkRabbani Aug 04 '22 at 06:20
  • Hi @mkRabbani . The column “sum of each datetime” is not a physical column. It is created virtually using the summarize function. I wrote its formula at the end of my post. – Parham.Trader Aug 04 '22 at 06:40

1 Answers1

1

You can create one measure and one column as given below.

Column:

date_sum_column = 

var current_row_date_time = ('your_table_name'[DateTimeStamp])

return
CALCULATE(
    SUM('your_table_name'[Value]),
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[DateTimeStamp] = current_row_date_time
    )
)

Measure:

running_max_sum = 
CALCULATE(
    MAX(your_table_name[date_sum_column]),
    FILTER(
        ALL(your_table_name),
        your_table_name[DateTimeStamp] <= MIN(your_table_name[DateTimeStamp])
    )
)

Here is the output:

enter image description here

mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • Thank you so much for your help. But please be noted that I currently have the SUM OF EACH DATETIME column as a virtual column in my summarized table and I WANT ANOTHER COLUMN BESIDE IT TO CALCULATE THE RUNNING MAX OF THE VALUES IN THE "SUM OF EACH DATETIME" BEFORE THE CURRENT ROW IN THE TABLE. – Parham.Trader Aug 04 '22 at 09:37
  • Yes, I did the same. Just created make your SUM OF EACH DATETIME physical to achieve the purpose. – mkRabbani Aug 04 '22 at 12:21