Two Tables with different date ranges (Weekly & Daily) to be merged into the same line graph

[Anonymous]

Hi All;,

I need to make a line graph with price on Y axis and weekly start dates(Monday) on the X axis. However I have two tables, one contains prices in a weekly format (see table below) and the other is prices in a daily format.

I have tried the USERELATIONSHIP DAX query to change the daily values(Table 2) into weekly values in Table 1

I know i have to create a relationship between the two tables to get this to work but I'm having trouble getting the correct start of week values without an error popping up.

Can anyone help?

stats_livestock_price.stat_week	stats_livestock_price.average_price	Start of week values Table 2
01/04/2019	4.562	#ERROR
08/04/2019	4.723	#ERROR
15/04/2019	4.682	#ERROR
22/04/2019	4.658	#ERROR
29/04/2019	4.621	#ERROR
06/05/2019	4.547	#ERROR
13/05/2019	4.613	#ERROR
20/05/2019	4.982	#ERROR
27/05/2019	4.79	#ERROR
03/06/2019	4.725	#ERROR
10/06/2019	4.515	#ERROR
17/06/2019	4.516	#ERROR
24/06/2019	4.497	#ERROR
01/07/2019	4.335	#ERROR
08/07/2019	4.149	#ERROR
15/07/2019	4.146	#ERROR
22/07/2019	3.995	#ERROR
29/07/2019	3.955	#ERROR
05/08/2019	3.952	#ERROR
12/08/2019	3.944	#ERROR
19/08/2019	3.92	#ERROR
26/08/2019	3.919	#ERROR
02/09/2019	3.866	#ERROR
09/09/2019	3.789	#ERROR
16/09/2019	3.742	#ERROR
23/09/2019	3.723	#ERROR
30/09/2019	3.712	#ERROR
07/10/2019	3.713	#ERROR
14/10/2019	3.743	#ERROR
21/10/2019	3.735	#ERROR
28/10/2019	3.771	#ERROR
04/11/2019	3.9	#ERROR
11/11/2019	4.073	#ERROR
18/11/2019	4.215	#ERROR
25/11/2019	4.233	#ERROR
02/12/2019	4.274	#ERROR
09/12/2019	4.317	#ERROR
16/12/2019	4.298	#ERROR
23/12/2019	4.38	#ERROR
30/12/2019	4.501	#ERROR
06/01/2020	4.503	#ERROR
13/01/2020	4.547	#ERROR
20/01/2020	4.634	#ERROR
27/01/2020	4.71	#ERROR
03/02/2020	4.966	#ERROR
10/02/2020	5.228	#ERROR
17/02/2020	5.32	#ERROR
24/02/2020	5.303	#ERROR
02/03/2020	5.309	#ERROR
09/03/2020	5.397	#ERROR
16/03/2020	5.514	#ERROR
23/03/2020	5.166	#ERROR

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487#M123

[Anonymous]

I've followed your code and come up with:

mWeekStarting1 =

//Get information about the current date

VAR myDate = MAX(Sheet1[Intake_Data_Kill_Date])

VAR myWeekNum = WEEKNUM(myDate)

VAR myYear = YEAR(myDate)

//Set min and max for Calendar table

VAR minDate = CALCULATE(MIN(Sheet1[Intake_Data_Kill_Date]),ALL(Sheet1))

VAR maxDate = CALCULATE(MAX(Sheet1[Intake_Data_Kill_Date]),ALL(Sheet1))

//Create calendar table and add required columns

VAR dateTable = CALENDAR(minDate,maxDate)

VAR dateTable1 = ADDCOLUMNS(dateTable,"WeekNum",WEEKNUM([Date]))

VAR dateTable2 = ADDCOLUMNS(dateTable1,"WeekDay",WEEKDAY([Date]))

//Return date that matches the current year and weeknum and is a week day of 1 (Sunday)

VAR weekStartDate = MINX(FILTER(dateTable2,YEAR([Date])=myYear&&[WeekNum]=myWeekNum&&[WeekDay]=1),[Date])

//If null, then it is at the end of the year and the week starts last year - there may be 53 weeks that year

VAR weekStartDate1 = IF(NOT(ISBLANK(weekStartDate)),weekStartDate,MINX(FILTER(dateTable2,YEAR([Date])=myYear-1&&[WeekNum]=53&&[WeekDay]=1),[Date]))

//If null, then it is at the end of the year and the week starts last year - there may not be 53 weeks that year

VAR weekStartDate2 = IF(NOT(ISBLANK(weekStartDate1)),weekStartDate1,MINX(FILTER(dateTable2,YEAR([Date])=myYear-1&&[WeekNum]=52&&[WeekDay]=1),[Date]))

//If it is still null, then it is at the start of our calendar table

VAR weekStartDate3 = IF(NOT(ISBLANK(weekStartDate2)),weekStartDate2,MINX(dateTable2,[Date]))

//Return a nicely formatted week ending format "W# Week Ending mm/dd/yyyy"

RETURN "W" & myWeekNum & " Week Starting " & weekStartDate3

However this gives me 0 values along my graph

[Anonymous]

This is a job for Power Query, to be honest, not DAX. And please watch this:

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

https://www.youtube.com/watch?v=_quTwyvDfG0

Best
D