Forum Discussion

Jeremyh's avatar
Jeremyh
Frequent Visitor
3 years ago
Solved

DAX: Days Between Today and specific Transaction Date Not Calculating Correctly

I Have a two Tables, Table1 contains 1 record per Consignment (order) and Table 2 which contains many Scan Transactions with a Branch (Location) and Scan Date.

 

 

I Need help fixing my DAX Statement. I want to calculate the Number of Days between the First (Earliest) Scan Date where the [Scan Branch] matches the Table1 [Destination Branch]. In The above example I want to calculate the number of days between 19/04/2023 and today (28/6/2023) which = 70

 

However my DAX is returning 57, Which is the number of days between the Last (most recent) Transaction scan and Today.

 

 

Days between Earliest Scan = 
VAR EarliestScanDate =
    MINX(
        FILTER(
            'Table 2',
            'Table 2'[Scan Branch] = RELATED(Table 1'[Destination Branch])
        ),
        'Table 2'[Date of Scan]
    )
RETURN
    IF(
        NOT ISBLANK(EarliestScanDate),
        DATEDIFF(EarliestScanDate, TODAY(), DAY),
        BLANK()
    )

 

 

Need Help
  • danextian's avatar
    danextian
    3 years ago

     

    Hi Jeremyh,

     

    You can use ALLEXCEPT to aggregate rows based on an item in a column. Try something like this:

    =
DATEDIFF (
    CALCULATE (
        MIN ( 'table'[date] ),
        ALLEXCEPT ( 'table', 'table'[consignment number] )
    ),
    TODAY (),
    DAY
)

2 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

     

    Hi Jeremyh,

     

    You can use ALLEXCEPT to aggregate rows based on an item in a column. Try something like this:

    =
DATEDIFF (
    CALCULATE (
        MIN ( 'table'[date] ),
        ALLEXCEPT ( 'table', 'table'[consignment number] )
    ),
    TODAY (),
    DAY
)
    • Jeremyh's avatar
      Jeremyh
      Frequent Visitor
      3 years ago

      Thanks danextian appreciate the response and the explaination. I've discovered the issue is with a slicer that was filtering the Last Record. When I turn this off I get the correct Number of Days.