Summarize values and compare total day values over a range of data

[SJ]

Guys,

I am looking to create a formula that will caculate the MAX of sum of values by date, The output I would be looking for is:

If presented as Week:

Week 12 – 108 (i.e. 15-Mar)

Week 13 – 153 (i.e. 18-Mar)

If presented as Month:

Mar – 153 (i.e. 18 Mar)

Data sample

Report Date	SKU	Value
10/03/2020	A	10
10/03/2020	B	11
10/03/2020	C	12
11/03/2020	A	15
11/03/2020	B	16
11/03/2020	C	17
12/03/2020	A	20
12/03/2020	B	21
12/03/2020	C	22
13/03/2020	A	25
13/03/2020	B	26
13/03/2020	C	27
14/03/2020	A	30
14/03/2020	B	31
14/03/2020	C	32
15/03/2020	A	35
15/03/2020	B	36
15/03/2020	C	37
16/03/2020	A	40
16/03/2020	B	41
16/03/2020	C	42
17/03/2020	A	45
17/03/2020	B	46
17/03/2020	C	47
18/03/2020	A	50
18/03/2020	B	51
18/03/2020	C	52

Can anyone help please?

[v-yuta-msft]

@SJ ,

---

If presented as Week:

Week 12 – 108 (i.e. 15-Mar)

Week 13 – 153 (i.e. 18-Mar)

If presented as Month:

Mar – 153 (i.e. 18 Mar)

---

I'm confused on this. Could you please clarify more details about the meaning of 108 and 153?

Regards,

Jimmy Tao

[Greg_Deckler · Accepted solution]

So your quotes are in the wrong format for me and it's a real pain to convert them and I don't understand your weeks and what you're saying there. In my calendar, March 15 and 18 are in the same week. But, you could do things like:

Measure = 
  VAR __Table = 
    SUMMARIZE(
      'Table',
      [Report Date],
      "__Value",SUM([Value])
    )
  VAR __Max = MAXX(__Table,[__Value])
RETURN
  MAXX(FILTER(__Table,[__Value] = __Max),[Report Date])

[SJ]

Greg,

Just changed the Report Date at the end of the formula to value and it works a treat. Thanks a bunch!

Soren

[SJ]

Hi Greg,

Sorry about the week, Our fiscal year is not aligned to the normal calendar and the week starts on a Monday.

I think you are on the right track as it does return the 18th as the date. Can you also make it return the value for the highest date - i.e. 153 for the 18th?

[Greg_Deckler]

Hard to tell exactly, but sounds like you want a variation of: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814