Forum Discussion
Fiscal Week Calendar Help
Hello, I want to calculate WEEKNUM but from the date I provide. Table 1: Date: 25/07/2019 Table 2: Date of Sign Up: 29/07/2019 Date of Sign Up: 02/08/2019 Date of Sign Up: 24/12/201...
Anonymous wrote:Hi Anonymous ,
Can you please upload some data for Table1 and Table2 and the output expected on Goodlge / One Drive and share the link here to find the right solution.
Cheers
CheenuSing
Hi Anonymous
So there are two options, I can either use two tables or I can append a column of Date Sent into a single table, now called Table 3, then I do not need to use Table 1 or Table 2.
hi Anonymous ,
Yes you have two options as mentioned.
You don't need to create Table3, if yo so wish.
The Table 2 can be populated with a calculated column using
C_ByWhichWeek =
VAR _dateSent =
LOOKUPVALUE ( Table1[Date Sent], Table1[ID], Table2[ID] )
RETURN
DATEDIFF ( _dateSent, Table2[Sign Up], WEEK )
Or with the relationship establised between Table1 and Table2 on ID you can create a measure
M_ByWhichWeek =
VAR _curSignup =
SELECTEDVALUE ( Table2[Sign Up] )
VAR _curID =
SELECTEDVALUE ( Table2[ID] )
VAR _dateSent =
LOOKUPVALUE ( Table1[Date Sent], Table1[ID], _curID )
RETURN
DATEDIFF ( _dateSent, _curSignup, WEEK )
Sample output using the data.
If this is what you expected, please accept it as a solution.
Cheers
CheenuSing