Forum Discussion

yasbos's avatar
yasbos
Icon for Resolver II rankResolver II
8 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
    8 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
8 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
8 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
    8 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
    )