userelationship

[Anonymous]

Hello Community  -  I have a measure below that I need to modify, just not sure how.   The "sum"  of the qty ordered that I am trying to find needs to be based on the "request date".    Currently my date table is connected to the "due date".   

So, I'd like to use the date table "year and month" field, but I need the values from the measure to reflect the values based on the context of request date, not due date.    Also, I have another measure in the visual that also relies on the date table, so it is important that I use the date table.    Is there a way to incorporate the USERELATIONSHIP function into this formula below? 

Start of Month minus 2 =

VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))

VAR nTable = FILTER(vTable,Orders[Order Date Month] <= Orders[Request Date Month] -2 || Orders[Order Date Month] <= Orders[Request Date Month] +10 && Orders[Order Date Year] < Orders[Request Date Year])

VAR _Result = SUMX(nTable,[SumX of Qty Ordered])

RETURN

_Result

[mwegener]

Hi @Anonymous ,

in your statement you use ALL to remove the filter from the date table. I think if you want to work with date reference, you have to replace this with the USERELATIONSHIP.

[Anonymous]

@mwegener    I have tried this.   I actually need to use two userelationships because request date and order date are both connected to the date table using inactive relationships.  

But I am getting an error message: 

[mwegener]

Hi @Anonymous ,

sorry I think I misunderstood your requirement and your DAX statement.

In my opinion, I would not do the date comparison of Order Date and Request Date using the date dimension.
What advantage do you see to do this via the date dimension?

[Anonymous]

Hi @mwegener       What the formula is trying to do is to look at the request date (the date an order has been requested to ship) and look two months back to the order date.    We are trying to see how accurate our forecasting is and this is the logic they want to use.   So, if there are orders with requests dates in May, we would want to look at orders with order dates in March (two months back).     However, the Orders table has a primary active relationship with the date table...using "due date".    So in essence, the date table context on the orders table, at least for this particula formula, is using the wrong date context  (due date instead of request or order date).    I am trying to get the formulas to connect to the date table each with their respective date context  (either request date or order date).   Hope that makes sense? 

[mwegener]

Hi @Anonymous ,

can you provide sample data and describe what the result should look like? The DAX statement seems a bit too complex to me.