Group days into week numbers and find their grouped sum

[Anonymous]

I have a column 'Day' which represents the campaign day of each month, another column called 'Month' which has two months Nov and Dec and two other columns 'CurrentTotal' and 'PastTotal' which has the transactional amount tied to the respective campaign day of each month.  Below is how my sample data looks like:

Year	Month	Day	CurrentTotal	PastTotal
2019	Nov	1	$0	$0.1
2019	Nov	2	$0.2	$0
2019	Nov	3	$5	$1
2019	Nov	4	$19	$8
2019	Nov	5	$4	$9.5
2019	Nov	6	$0	$15
2019	Nov	7	$6	$20
2019	Nov	8	$4.3	$21
2019	Nov	9	$7	$19
2019	Nov	10	$2	$12
2019	Nov	11	$0	$0
2019	Nov	12	$0	$0
2019	Nov	13	$8	$2.5
2019	Nov	14	$6.5	$6
2019	Nov	15	$3	$4
2019	Nov	16	$0	$0
2019	Nov	17	$0	$1
2019	Nov	18	$9	$5.5
2019	Nov	19	$2	$4
2019	Nov	20	$4	$3
2019	Nov	21	$7.6	$3.5
2019	Nov	22	$3	$0
2019	Nov	23	$0	$1.5
2019	Nov	24	$0	$0
2019	Nov	25	$0	$0
2019	Nov	26	$11	$7
2019	Nov	27	$13	$10
2019	Nov	28	$14	$11
2019	Nov	29	$14.5	$8.5
2019	Nov	30	$0	$0
2019	Dec	1	$9.8	$0
2019	Dec	2	$7.6	$6
2019	Dec	3	$0	$13
2019	Dec	4	$5	$9

As seen in the table above, there are 30 days in Nov, similarly, there will be 31 days in Dec (I have avoided writing all the 31 days to avoid making the table big). Also, the column 'Year' has values for 2019 and 2020. So there will be transactions in Nov 2019, Dec 2019, Nov 2020 and Dec 2020. Now, I want to create new variables which groups the campaign days into week numbers so that there will be total 9 different weeks starting from Nov and ending in Dec , for example, campaign day 1-7 in Nov will fall into week 1, campaign day 8-14 in Nov will fall into week 2 and so on. A twist over here however is that since the campaign day for Dec starts at 1, I do not want to attribute week 1 to the first campaign day of Dec, it will rather be a continuation from Nov and hence a total of 9 weeks and finally sum the currenttotal and pasttotal by each week number.

Below is how I would want my final table to look like:

Year	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9
2019	53.6	60.5	and so on
2020	34.2	27.8	and so on

[AlB]

@Anonymous 

I'm lost. What table are you showing? Is this a visual of the real table? What are 'ReportedTotalDate' and 'ReportedDate'??

You simply need to create a relationship between AuxDateT[Date] (the table newly created with the week info) and FactTable[Date], the column in your fact table that determines what dates you'll operate on. That should be it it. @HotChilli has explained it quite well. Share the pbix if it doesn't work

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

Be more specific. What exactly is not matching? The relationship should be between the Date columns from each table.

You could do it in DAX, but PQ is best for this

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB I have created a many to one relationship between the two tables. As we can see in the screenshot provided above, values from 'ReportedTotalDate' do not match with values in 'ReportedDate' column. For example, if we look at the 4th row, the value under 'ReportedTotalDate' is 11/29/2019 whereas that under 'ReportedDate' is '11/30/2019'. Shouldn't the two values be same after establishing relationship between the two tables?

[Anonymous]

@HotChilli Thank you for your response! As suggested by you, I created two date tables in Power query and merged them. Next, when I tried making a connection (Many to one relationship since my original dataset has multiple occurrences of date while there are unique date values in the newly created date tables ) between my original table and the newly created table, they are not matching properly. I'm providing a screenshot below:

How can I solve this? Also, instead of creating new tables, can we create a new column using DAX which auto increments itself starting at 0? And later I can use quotient() to carry out the same functionality as that of Number.IntegerDivide()? Kindly let me know your thoughts on this. Your help is greatly appreciated!

[HotChilli · Accepted solution]

This is all about creating a custom date table.  You can do it in Power Query.

Get a table with a column of dates 2019Nov1 - 2019Dec31.

You need to add a column in the date table to represent WeekNum.  You could do this by adding an Index column from 0.

Add a weeknum column with a formula like Number.IntegerDivide([Index], 7) + 1

Number.IntegerDivide([Index], 7) + 1

Add a Year column.  This will be a date table with 2019 dates.

-----

Do the same steps for a date table from Nov 1 2020 - Dec 31 2020.

---

Then append the 2 date tables together, (so you will have 1 date table with dates Nov1 2019 -> Dec 31 2019 and Nov 1 2020 -> Dec 31 2020)

Merge the Year , Month, Day columns in the data table and set them to date type.  Relate this to the date column in the custom date table (in powerbi relationship screen).

------

You can then create a matrix with Year (from the date table ) in Rows.  WeekNum in Columns .

CurrentTotal and pastTotal can be SUMmed and put in the Values section.

Good luck