How to Get Min Value of three months

vp3681

New Member
Joined
Apr 7, 2015
Messages
17
I have data of few months.
DateNameValue
01-04-2018a2
03-04-2018b2
05-04-2018c8
07-04-2018d3
09-04-2018e1
11-04-2018f3
13-04-2018g10
15-04-2018h9
17-04-2018i5
19-04-2018j7
21-04-2018k4
23-04-2018l5
25-04-2018m8
27-04-2018a4
29-04-2018b10
01-05-2018c8
03-05-2018d5
05-05-2018e3
07-05-2018f2
09-05-2018g7
11-05-2018h2
13-05-2018i3
15-05-2018j6
17-05-2018k4
19-05-2018l9
21-05-2018m9
23-05-2018a1
25-05-2018b3
27-05-2018c2
29-05-2018d8
31-05-2018e1
02-06-2018f7
04-06-2018g5
06-06-2018h4
08-06-2018i9
10-06-2018j5
12-06-2018k3
14-06-2018l4
16-06-2018m10
18-06-2018a5
20-06-2018b8
22-06-2018c4
24-06-2018d4
26-06-2018e3
28-06-2018f6
30-06-2018g2
02-07-2018h8
04-07-2018i10
06-07-2018j3
08-07-2018k7
10-07-2018l6
12-07-2018m6

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?

NameAprilMayJuneJulyMinimum
a
2​
1​
5​
1​
b
2​
3​
8​
2​
c
8​
2​
4​
2​
d
3​
5​
4​
3​
e
1​
1​
3​
1​
f
3​
2​
6​
2​
g
10​
7​
2​
2​
h
9​
2​
4​
8​
2​
i
5​
3​
9​
10​
3​
j
7​
6​
5​
3​
3​
k
4​
4​
3​
7​
3​
l
5​
9​
4​
6​
4​
m
8​
9​
10​
6​
6​
 
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]

NameAprilMayJuneMinimum
a
2​
1​
5​
1​
b
2​
3​
8​
2​
c
8​
2​
4​
2​
d
3​
5​
4​
3​
e
1​
1​
3​
1​
f
3​
2​
6​
2​
g
10​
7​
2​
2​
h
9​
2​
4​
2​
i
5​
3​
9​
3​
j
7​
6​
5​
5​
k
4​
4​
3​
3​
l
5​
9​
4​
4​
m
8​
9​
10​
8​
 
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

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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