How to Get Min Value of three months

V

vp3681

New Member
Joined
Apr 7, 2015
Messages
17
I have data of few months.
[TABLE="width: 201"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2018[/TD]
[TD]a[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]03-04-2018[/TD]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]05-04-2018[/TD]
[TD]c[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]07-04-2018[/TD]
[TD]d[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]09-04-2018[/TD]
[TD]e[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11-04-2018[/TD]
[TD]f[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]13-04-2018[/TD]
[TD]g[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]15-04-2018[/TD]
[TD]h[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]17-04-2018[/TD]
[TD]i[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]19-04-2018[/TD]
[TD]j[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]21-04-2018[/TD]
[TD]k[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]23-04-2018[/TD]
[TD]l[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]25-04-2018[/TD]
[TD]m[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]27-04-2018[/TD]
[TD]a[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]29-04-2018[/TD]
[TD]b[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]01-05-2018[/TD]
[TD]c[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]03-05-2018[/TD]
[TD]d[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]05-05-2018[/TD]
[TD]e[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]07-05-2018[/TD]
[TD]f[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]09-05-2018[/TD]
[TD]g[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]11-05-2018[/TD]
[TD]h[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]13-05-2018[/TD]
[TD]i[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]15-05-2018[/TD]
[TD]j[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]17-05-2018[/TD]
[TD]k[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]19-05-2018[/TD]
[TD]l[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]21-05-2018[/TD]
[TD]m[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]23-05-2018[/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]25-05-2018[/TD]
[TD]b[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]27-05-2018[/TD]
[TD]c[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]29-05-2018[/TD]
[TD]d[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]31-05-2018[/TD]
[TD]e[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]02-06-2018[/TD]
[TD]f[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]04-06-2018[/TD]
[TD]g[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]06-06-2018[/TD]
[TD]h[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]08-06-2018[/TD]
[TD]i[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]10-06-2018[/TD]
[TD]j[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]12-06-2018[/TD]
[TD]k[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]14-06-2018[/TD]
[TD]l[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]16-06-2018[/TD]
[TD]m[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]18-06-2018[/TD]
[TD]a[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20-06-2018[/TD]
[TD]b[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]22-06-2018[/TD]
[TD]c[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]24-06-2018[/TD]
[TD]d[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]26-06-2018[/TD]
[TD]e[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]28-06-2018[/TD]
[TD]f[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]30-06-2018[/TD]
[TD]g[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]02-07-2018[/TD]
[TD]h[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]04-07-2018[/TD]
[TD]i[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]06-07-2018[/TD]
[TD]j[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]08-07-2018[/TD]
[TD]k[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]10-07-2018[/TD]
[TD]l[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]12-07-2018[/TD]
[TD]m[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

Having three columns - Date, Product and Value
I want to min value out of April, May and June by using Measure.

I want to create Pivot table Row Product, Column Month and last column Min value out of April, May and June.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hi,

Is it the minimum at the aggregated or row level you need?

For example, April contains the Name 'a' twice, with one value being 2 and one value being 4. Should the minimum for April be 2 or are you only interested in the aggregated amount of 6?

Matty
 
Upvote 0
I need only Minimum number against "a" from all three month.
Actually, I need pivot table with Product Name in Row, and five column.

Column -1 - April - Min (I know how to get it)
Column -2 - May - Min (I know how to get it)
Column - 3 June - Min (I know how to get it)
Column - 4 July - Min (I know how to get it)
Column - 5 - Min number from April, May and June (It should be from Dax Measure)
 
Last edited:
Upvote 0
I'm not sure but something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]April[/td][td=bgcolor:#70AD47]May[/td][td=bgcolor:#70AD47]June[/td][td=bgcolor:#70AD47]July[/td][td=bgcolor:#70AD47]Minimum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]
2​
[/td][td]
3​
[/td][td]
8​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]d[/td][td]
3​
[/td][td]
5​
[/td][td]
4​
[/td][td][/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]e[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]f[/td][td]
3​
[/td][td]
2​
[/td][td]
6​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]g[/td][td=bgcolor:#E2EFDA]
10​
[/td][td=bgcolor:#E2EFDA]
7​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]h[/td][td]
9​
[/td][td]
2​
[/td][td]
4​
[/td][td]
8​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]i[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]j[/td][td]
7​
[/td][td]
6​
[/td][td]
5​
[/td][td]
3​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]k[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
7​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]l[/td][td]
5​
[/td][td]
9​
[/td][td]
4​
[/td][td]
6​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]m[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][td=bgcolor:#E2EFDA]
6​
[/td][td=bgcolor:#E2EFDA]
6​
[/td][/tr]
[/table]
 
Upvote 0
Yes the expected Pivot table structure is correct.
But I need minimum of April, May & June only.. July not to be considered in calculation.
 
Last edited:
Upvote 0
sure, but why PivotTable?

with PowerQuery:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Name", type text}, {"Value", Int64.Type}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Date", each Date.MonthName(_), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Month Name", each ([Date] <> "July")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Date]), "Date", "Value", List.Min),
    #"Inserted Minimum" = Table.AddColumn(#"Pivoted Column", "Minimum", each List.Min({[April], [May], [June]}), Int64.Type)
in
    #"Inserted Minimum"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]April[/td][td=bgcolor:#70AD47]May[/td][td=bgcolor:#70AD47]June[/td][td=bgcolor:#70AD47]Minimum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]
2​
[/td][td]
3​
[/td][td]
8​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]d[/td][td]
3​
[/td][td]
5​
[/td][td]
4​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]e[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]f[/td][td]
3​
[/td][td]
2​
[/td][td]
6​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]g[/td][td=bgcolor:#E2EFDA]
10​
[/td][td=bgcolor:#E2EFDA]
7​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]h[/td][td]
9​
[/td][td]
2​
[/td][td]
4​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]i[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]j[/td][td]
7​
[/td][td]
6​
[/td][td]
5​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]k[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]l[/td][td]
5​
[/td][td]
9​
[/td][td]
4​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]m[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][td=bgcolor:#E2EFDA]
8​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Great thanks, really appreciate your suggestion.
But still, I would like to learn DAX formula if possible.
 
Upvote 0
Hi vp3681,

Try this:

(1) Add a calculated column to PowerPivot with the formula MONTH(Assignment[Date])
(2) Add Measure as follow: MinMonth:=CALCULATE( MIN(Assignment[Value]),
Filter(
Assignment,
Assignment[Month]=4 ||
Assignment[Month]=5 ||
Assignment[Month]=6
)
)
(3) Pivot the data as follows: Row Name, Values MinMonth

Note: Assignment is the name of the table and Month is the Calculated Column Name
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Power Query chose a value <=date
Replies
1
Views
208
AlienSx
A
K
  • Question Question
Power bi Calculate the Monday dates
Replies
1
Views
156
GraH
GraH
A
  • Solved
help required with complex (well, i think so) statement in power query
Replies
3
Views
299
ajm
A
G
  • Solved
Rolling min and max column based on column
Replies
5
Views
504
JEC
JEC
R
  • Question Question
Power Query Replace Values with IF statement
Replies
5
Views
422
RattlingCarp3048
R

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top