Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Using USERELATIONSHIP to activate another date field in weekly calculation

Hi all,

 

I'm trying to determine what my expected payments will be for the next week, based on an invoice due date.

 

That being said, the invoice due date is not the active filter to my master date table. I've already used the following formulas to determine payments received this week and last week (based on the [transaction date]).

 

Receivables This Week = 
VAR Current_Week = CALCULATE(MAX('MD Date Table'[Week Number]))
VAR Current_Year = CALCULATE(MAX('MD Date Table'[Year]))
RETURN
SUMX(
    FILTER(
        ALL('MD Date Table'),
        'MD Date Table'[Week Number] = Current_Week && 'MD Date Table'[Year] = Current_Year
    ),
[AR Amount]
)

 

Receivables Last Week = 
VAR Current_Week = CALCULATE(MAX('MD Date Table'[Week Number]))
VAR Current_Year = CALCULATE(MAX('MD Date Table'[Year]))
RETURN
SUMX(
    FILTER(
        ALL('MD Date Table'),
        'MD Date Table'[Week Number] = Current_Week -1 && 'MD Date Table'[Year] = Current_Year
    ),
[AR Amount]
)

 

It's not as simple as using "+1" on the current week as this would still be referring to the wrong date field ([Transaction Date]) which would be empty as the transaction has not occured.

 

Is this just a case of somehow including the USERELATIONSHIP formula within my existing formula to point to the [Invoice Due Date] field instead?

 

Please see schema below:

 

 

Any advice would be much appreciated!

 

Kind regards,

Aaron

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Refer to this article how use relation has been used

     

    Example

    Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • fhill's avatar
    fhill
    Resident Rockstar
    6 years ago

    While not directly answering your USERELATIONSHIP question, since it sounds like your dates are pretty limited for this one-off data search, have you tried a LOOKUPVALUE command?  Treating the new data as un-related?  (If you don't have a performance demand / large data sizes, this could help without the Linked Relationship?  Since these are dates also, it's not a Text Based search request, so it should work smoothly even in larger datasets?)

     

    https://dax.guide/lookupvalue/

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey fhill 

       

      Not sure I entirely understand where you're coming from. How exactly would I use LOOKUPVALUE in this example? I just want to activate a different date field within the same table (which contains transaction date and due date).

       

      The relationship with my date table is on the 'transaction date' field.

       

      I would like to be able to switch to the due date in order to determine the count & value of upcoming payments.

       

      Hope this makes more sense!

       

      Aaron

      • fhill's avatar
        fhill
        Resident Rockstar
        6 years ago

        I did some more digging, and it's probably better to use a Calculate off your Date Table, with FILTER(ALL( of the desired data table.  Here's an example below:

         

        I have a Date Table with Date & Month Name as a starting point, and it's in a Relationship with Transaction Date, like your example.

         

        Since i'm only connected to Transaction Date by default, when I go to SUM Amount, only rows with a Transaction Date properly add up.  To further show there's NO LINK between the Date Table and the data in question, I created a full Duplicate of the Invoice Table (2), and you can see it's not connected AT ALL to the Date Table.

         

        I can however, still touch this data, I just have to completly control how the table is searched.  This Future Sales Calculation (on the Date Table) Compares 'Date by Date' the Date Table [Date] to the Invoice Table (2) - Invoice Due Date...  again, completely regardless of the Transaction Date.  Everytime a match between the two dates are found, it SUMS Amount.

         

        You can build off of this then, if you are looking for future week sales...

        Future Sales = CALCULATE( SUM('Invoice Table (2)'[Amount]), FILTER(ALL('Invoice Table (2)'), 'Date Table'[Date] = 'Invoice Table (2)'[Invoice Due Date]))
         
        Hope this helps, 
        FOrrest