Peak Value in a given month

[sagar_r01]

Hi All,

I need some help with the calculations I am trying to create.

I have a few columns in my table in which I have Date and a MetricValue column.

I need to create separate measures for Current_Month, Previous_Month which will have a peak metric value for the current or previous month.

Example

The below table is the Date and Sum of Metric Value. I need result measure should give Current Month Peak Value = 190 and Previous Month Peak Value = 300.

I have other columns too which I should be able to filter on, for example, Country = US

Date	Sum(MetricValue)
1/1/2020	150
1/2/2020	180
1/3/2020	185
1/4/2020	190
1/5/2020	155
1/6/2020	140
1/7/2020	162
1/8/2020	135
1/9/2020	130
1/10/2020	180
12/1/2019	201
12/2/2019	250
12/3/2019	290
12/4/2019	230
12/5/2019	250
12/6/2019	300
12/7/2019	200
12/8/2019	256
12/9/2019	210
12/10/2019	200

Thanks in Advance

[sagar_r01]

Hi @amitchandak , Thanks for the reply

When i am using the MTD Sales = CALCULATE(maxx(values('Date'[Date]),[sum Metric]),DATESMTD('Date'[Date])) i am getting sum of metric value and not Peak value. i need to write sum([sum Metric]) in order to run the measure or it is giving error.

My DAX is as below

MTD Sales = CALCULATE(maxx(values('Usage'[Date]),SUM([Metric Value])),DATESMTD('Usage'[Date]))

as per my original example i was expecting to get 190 (peak value on 1/4/2020)

but instead i am getting total of the month i.e 1607

Thanks

[edhans]

It is essential you have a date table. I generally construct date tables this way.

Once you do that, if you are putting the month number in a table or matrix, or whatever, you simply use the MAX function.

For the previous month, you could use:

Previous Month Value = 
    CALCULATE(
        [Peak  Metric Value],   <----- Just a measure that uses MAX(tablename[fieldname])
        PREVIOUSMONTH('Date'[Date])
    )

You can see my full model here. Note how the model looks for relationships, and the date table is marked as a date table. It gives the following:

[sagar_r01]

Hi @edhans , Thanks for the reply, the solution you gave, it will work only if i have Date and Metric Value column. As i mentioned in my earlier post, my table granularity is more than 2 columns. The function you gave gives only max of value in particular row but does not aggregated to date and finds the peak in given day of month.

The table which i gave as example is just aggregated at 2 columns.

Thanks

[amitchandak]

Please find the link :https://www.dropbox.com/s/gig0zuv59y67q13/MaxMetrics.pbix?dl=0

When you use time intelligence a month context is needed to get month data. If you do not use a slicer to filter for a  date, month or year from the calendar then it will take the last date of the calendar as reference point. I created the calendar till max date you have.

If you plot by month year, and use this month and last month measures, it will take row context. Make sure it should be in the calendar table, We are passing dates from the calendar table to time intelligence function datesmtd.

[sagar_r01]

@amitchandak- Thanks for the quick reply.

As i mentioned in my original question, i have lot of columns out of which i showed only 2 aggregated columns Date and SumMetric.

if i take below example and apply your formula i will get 110, but i need the peak value based on date on aggregated SumMetric

my expected result is 190 for 1/4/2020. Again below is example with fewer columns, but my table has lot of columns.

Current Month Peak = 190 (i need in single measure. Similar to what you showed in your example)

Date	Sum(MetricValue)	Country	State
1/1/2020	70	US	WA
1/1/2020	75	US	CA
1/2/2020	90	US	WA
1/2/2020	90	US	CA
1/3/2020	95	US	WA
1/3/2020	90	US	CA
1/4/2020	95	US	WA
1/4/2020	95	US	CA
1/5/2020	50	US	WA
1/5/2020	95	US	CA
1/6/2020	50	US	WA
1/6/2020	90	US	CA
1/7/2020	72	US	WA
1/7/2020	90	US	CA
1/8/2020	110	US	WA
1/8/2020	25	US	CA
1/9/2020	100	US	WA
1/9/2020	30	US	CA
1/10/2020	90	US	WA
1/10/2020	90	US	CA

[amitchandak · Accepted solution]

Refer the file at same location I shared last time

This Month 3 = maxx(SUMMARIZE(CALCULATETABLE(Sheet2,DATESMTD('Date'[Date])),'Date'[Date],"_sum",sum(Sheet2[Sum(MetricValue)])),[_sum])

[sagar_r01]

@amitchandak- you are genius!

[amitchandak]

Make sure you have date table create and join with table

MTD Sales = CALCULATE(maxx(values('Date'[Date]),[sum Metric]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(maxx(values('Date'[Date]),[sum Metric]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(maxx(values('Date'[Date]),[sum Metric]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

MTD Sales = maxx(summarize(CALCULATETable(Table,DATESMTD('Date'[Date])),DATESMTD('Date'[Date]),"_sum",[sum Metri]),[_sum])
last MTD Sales = maxx(summarize(CALCULATETable(Table,DATESMTD(dateadd('Date'[Date],-1,MONTH))),DATESMTD('Date'[Date]),"_sum",[sum Metri]),[_sum])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin