Forum Discussion

yasbos's avatar
yasbos
Icon for Resolver II rankResolver II
7 months ago
Solved

Date relationship issues

Hi. I have this weird issue with dates. I have an accounts table that is related to the Account[Date Opened] *-->1  _date[Date] field in the _date table. The relationship is inactive. However, I acti...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

    Could you please try this: 

    Traded Accts D7 =
CALCULATE(
    DISTINCTCOUNT(account[Account Number]),
    USERELATIONSHIP(_date[Date], account[Date Opened]),
    CROSSFILTER(account[Account Number], Assets[Account Number], NONE),
    FILTER(
        account,
        NOT ISBLANK(account[First Trade Date]) &&
        account[First Trade Date] <= account[Date Opened] + 7
    )
pcoley's avatar
pcoley
Icon for Super User rankSuper User
7 months ago

Please try whit COUNTROWS DISTINCTCOUNT(account) instead of DISTINCTCOUNT(account[Account Number])

I hope this helps. if so please mark it as a solution. Kudos are welcome.

  • yasbos's avatar
    yasbos
    Icon for Resolver II rankResolver II
    7 months ago

    Thanks so much. I tried (below), but I unfortunately still got the same wrong number (322) instead of 328.

     

    Traded Accts D7 =
    CALCULATE(
        COUNTROWS(account),
        USERELATIONSHIP(_date[Date], account[Date Opened]),
        FILTER(
            account,
            NOT ISBLANK(account[First Trade Date]) &&
            account[First Trade Date] <= account[Date Opened] + 7
        )
    )
    • cengizhanarslan's avatar
      cengizhanarslan
      Icon for Super User rankSuper User
      7 months ago

      Could you please try this: 

      Traded Accts D7 =
CALCULATE(
    DISTINCTCOUNT(account[Account Number]),
    USERELATIONSHIP(_date[Date], account[Date Opened]),
    CROSSFILTER(account[Account Number], Assets[Account Number], NONE),
    FILTER(
        account,
        NOT ISBLANK(account[First Trade Date]) &&
        account[First Trade Date] <= account[Date Opened] + 7
    )
  • yasbos's avatar
    yasbos
    Icon for Resolver II rankResolver II
    7 months ago

    Here is an update. I tried to activate the relationship between the Account[Date Opened] and _date[Date]. That gave me an error "multiple paths ... " and mentioned the other path as being through the Assets[Account Number] and the Account[Account Number] which is an active relationship. I then, just for the sake of testing, made this relationship inactive and then was able to make the account[Date Opened] and _date[Date] active. that immediately solved the problem. I now get 328. Does anyone know what was happening. I mean, I can't just leave the relationship between Account and Assets inactive because that will most likely break other measures. Thanks.

  • yasbos's avatar
    yasbos
    Icon for Resolver II rankResolver II
    7 months ago

    Second update: it seems that when I changed the crossfilter between the Account and the Assets tables from both to single that that fixed the problem too without having to deactivate this relationship.