Fiscal Week Calendar Help

---

Anonymous 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 Anonymous  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.