Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/2019
I would like to create a column in Table 2 with WEEKNUM using the date in Table 1.
How would I go about doing this.
Hi @Anonymous
What is the logic to be applied to put the weeknum in Table 2 based on Table 1 Value.
Will Table 1 will have only one value always ?
Cheers
CheenuSing
Hi @CheenuSing
Table 1: is the selection of people who were sent communications. Every one was sent the communication on the same day so the date will be static.
Table 2: Is the sign ups with amount paid and othe related financial information
Both tables are tables in SQL Server.
Thanks
Hi @Anonymous
Try this
ByWhichWeek = Calculate( WEEKNUM(MIN([SignUp])) - WEEKNUM( MAX(Table1[Date])) )
This assumes there is only one row in Table 1 and many rows in Table1 and both are not related.
Is this what you expected.
Cheers
CheenuSing
Hi @CheenuSing
Here's a question for you. There are a few rows, around 5 that exist in table 2 but not table 1. This causes the calculation to be wrong. Is there a way to use a fixed date if (Table 1)[Date] does not exist.
Otherwise I was thinking to add a column in the table 2 for Date as it will be static and use the below formula however the 40 is static.
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
@CheenuSing 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 @CheenuSing
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
@CheenuSing wrote: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
Hi @CheenuSing This does not work because there may be individuals in Table 2 that do not exist in Table 1 and that's where the DAX code is failing.
So I have a ID in Table 2 with a Sign up date, this ID does not exist in Table 1. So upon applying Dax, it either calculates the wrong number, or leaves the cell blank.
What I am trying to do is use a fallback value when the DAX formula is calculating. I hope you understand what I am saying.
Hi @Anonymous ,
For those unmatched ID values of Table2, what is the default value you want to set for SentDate ?
Cheers
CheenuSing
@CheenuSing wrote:Hi @Anonymous ,
For those unmatched ID values of Table2, what is the default value you want to set for SentDate ?
Cheers
CheenuSing
HI @CheenuSing , 25/07/2019
The reason why I created Table 3 is because based on the communication, I can create a column that ouputs the Date Sent and then we have Date Sign up and Date Sent in one table. I just thought maybe this method would be easier as we would not need to pass a default value.
Thanks for all your help sir.
@CheenuSing wrote:Hi @Anonymous
Try this
ByWhichWeek = Calculate( WEEKNUM(MIN([SignUp])) - WEEKNUM( MAX(Table1[Date])) )This assumes there is only one row in Table 1 and many rows in Table1 and both are not related.
Is this what you expected.
Cheers
CheenuSing
Hi @CheenuSing This seems to do the trick, thanks.
FYI: not sure if the below will make a difference to the code as it seems to work perfectly.
There is more than one row and more than one column in Table 1, but the Date in Table 1 is static. Table 1 is communication Selection so includes what type of Comms they received, Customer ID
Date in table 2 is dynamic.
Table 1 and Table 2 have a relationship based on Customer ID.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |