Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
duane1
New Member

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

Hi,

 

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}})

duane1_0-1695677283968.png

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.

 

 

2 REPLIES 2
duane1
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.

 

Thanks  

v-xinruzhu-msft
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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors