Create a Table

[Anonymous]

Hi, I'm new to power bi. I want to create a table from following data set showing date range 6/01/2020 as 6/01/2020 and get average volume and 7/01/2020 - 11/01/2020 as 12/01/2020 and get avearge volume and so on so forth. I need help please. Cheers.

Date	Date Range	Week No	Volume
6/01/2020	6/01/2020	2	18.50
7/01/2020		3	17.20
8/01/2020		3	19.50
9/01/2020		3	14.27
10/01/2020		3	13.70
11/01/2020	12/01/2020	3	15.50
12/01/2020		4	16.30
13/01/2020		4	18.60
14/01/2020		4	19.36
15/01/2020		4	20.54
16/01/2020	17/01/2020	4	13.12
17/01/2020		5	17.26
18/01/2020		5	19.25
19/01/2020		5	20.23
20/01/2020		5	20.00
21/01/2020	22/01/2020	5	15.00

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, here is my test table:

Then create two columns:

Week No = IF('Table'[Date]=MIN('Table'[Date]),2,IF(DATEDIFF(MIN('Table'[Date]),'Table'[Date],DAY)/5-ROUND(DATEDIFF(MIN('Table'[Date]),'Table'[Date],DAY)/5,0)<=1,2+ROUNDUP(DATEDIFF(MIN('Table'[Date]),'Table'[Date],DAY)/5,0)))

Date Range = IF('Table'[Date]=MIN('Table'[Date]),MIN('Table'[Date]),IF(DATEDIFF(MIN('Table'[Date]),'Table'[Date],DAY)/5-ROUND(DATEDIFF(MIN('Table'[Date]),'Table'[Date],DAY)/5,0)=0,'Table'[Date]+1,BLANK()))

It shows:

Create a slicer table:

Slicer Table = FILTER(DISTINCT(SELECTCOLUMNS('Table',"Week No",'Table'[Week No],"Date Range",'Table'[Date Range])),[Date Range]<>BLANK())

Then create relationships between two tables by [Week No] link column as one-to-many:

Create a measure to calculate average volume values:

Measure = AVERAGE('Table'[Volume])

Choose [Date Range] from Slicer Table and this measure as a table visual, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Greg_Deckler]

So like this?

Table1 = GROUPBY('Table',[Week No],"Average",AVERAGEX(CURRENTGROUP(),[Volume]))