Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous

 

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.

 

ByWhichWeek =
VAR __fw = [Date] - 40 + 1
RETURN IF(__fw<=0,52+__fw,__fw)
 
Thanks
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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. 

     

    https://we.tl/t-eqSkhe6mGZ

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        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.