The DAX code calculating the sum of maximum values per day

2
votes

I would like to write a dax code in Power BI which calculate the sum of maximum values of each day. In one column there is a production data and on the second column there is the the date and time. The counter resets in the beginning of every day. I want to pick up the maximum value for each day and sum them up.

Like,

Sum = Max of Day1 + Max of Day 2 + ....... Max of Day N

How is it possible? Thanks in advance

Sample Data:

Date                    Daily Counter
2/1/2018 12:00:00 AM    1
2/1/2018 6:00:00 AM     2
2/1/2018 12:00:00 PM    3
2/1/2018 6:00:00 PM     4.5
2/2/2018 12:00:00 AM    1
2/2/2018 6:00:00 AM     3
2/2/2018 12:00:00 PM    6
2/2/2018 6:00:00 PM     9
2/3/2018 12:00:00 AM    5
2/3/2018 6:00:00 AM     6
2/3/2018 12:00:00 PM    12
2/3/2018 6:00:00 PM     18
1
excelexcel-formulapowerbidax
Please provide a sample copy of your data. Not the actual values but some test values and your expected output. – Siddharth Thanga Mariappan
Here is the sample data. imgur.com/a/nFJPIJ0 I want to calculate Total = Max of 'Day1' + Max of 'Day2'+Max of 'Day3)+ ...... In the given sample it should be Total = 4,5 + 9 + 18 = 31,5 – Toyga
Please edit your question include the sample data. Also, it's preferable to post data as text rather than an image. – Alexis Olson

1 Answers

3
votes

This will be a bit easier if you have a column that has just dates rather than datetime values. So first, create a calculated column (I'm assuming your table is called Data):

DateDay = DATEVALUE(Data[Date])

Now that we have that, let's write the measure.

MaxValue =
SUMX (
    SUMMARIZE ( Data, Data[DateDay], "MaxCount", MAX ( Data[Daily Counter] ) ),
    [MaxCount]
)

What this does is create a table that summarizes each day by taking the maximum count on each day. The SUMX then goes through each row in the summary table and adds up the maximum count for each day.

Note that this works not just for the total, but on each row in your visual as well since the Data table that gets passed into the SUMMARIZE is filtered by its evaluation context so the DateDay filter is preserved.

Sum of Max