Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

Group by Month and Week and find first and last values (list.first; list.last)



I have data for locations and there is an "Open Number" for several units,  each day of the year.  This number can go up or down each day.  I have grouped the data and I need the start value for whatever week or month I choose.  I have used the "list.first" function in the "advanced editor".  I can pick the year and week, and I get the correct number.  But if I pick the year and month, I do not get the correct number.


Data in power query looks like below - grouped (using List.First for the open count)

 Here is the formula from the advanced editor:

#"Grouped Rows" = Table.Group(#"Filtered Rows5", {"SCENARIONAME", "DATE", "ISOYear", "Month", "Week", "SITE", "UNIT"}, {{"OPEN COUNT", each List.First([OPENCOUNT]), type number}})


I would like to create a pivot table in excel that will show me the "Open Count" by week or by month.  I cannot get it to work.  Using the formula above, I only get the correct number when I choose week 39 of 2023 (the current week we have just started).  The other weeks/months seem to be summing all the open counts for each day of the week or month.


Thanks for your time.



New Member

Hi, @v-xinruzhu-msft 


I am looking for the value at the start of the week, or month.   I would like to have the option of either one in a pivot table in excel.  It is not always the min or the max of the week or month.  Looking at the above, the first line shows a total of 65,716 for that day.  Lets just say that is the first day of the month and the week.  But the next day, Jan 03, which is day two of the week and month, the balance is 65,702 for that unit.  And so on each day, generally it will reduce slightly or stay the same.  In my pivot table, I would like to see the opening balance for Week 1 or month 1, or whichever week or month is selected in the pivot table in excel.  It will not always be the smallest or largest.  sometimes the unit is added to, or stays the same.  I want the value from the first day of either period selected.  I am getting a "sum" of all the values in the week or month as it is now.



Community Support
Community Support

Hi @duane1 

Do you want to find the min/max value of the grups?

You can consider to use List.Min or List.Max to find it, if this cannot meet your requirements, can you provide the output you want? 


est Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.