Snapshot open and due days

[PowPow]

Hi everyone,

i'm trying to figure out if i can replace our daily snapshot table by using dax measures directly on top of the transactional table. It was easy to calculate the balance but i am having trouble calculating Open and Due days.

First of all here is the end goal based on the snapshot data for one document. In the end scenario the users would be able to filter this also on customers, aging buckets etc.

For reference, this is how a part of the snapshot table looks like for this particular document:

snapshot table

And this is the transactional table for that same document:

transactional

The data model with transactional table and just the date table:

The Balance measure works perfectly with the formula:

Receivables Balance :=
IF (
    MIN ( 'Calendar'[DateSK] )
        <= CALCULATE ( MAX ( Receivables[PostingDateSK] ); ALL ( Receivables ) );
    CALCULATE (
        SUM ( Receivables[Amount (LCY)] );
        USERELATIONSHIP ( Receivables[DetailedLedgerPostingDateSK]; 'Calendar'[DateSK] );
        FILTER (
            ALL ( 'Calendar'[DateActual] );
            'Calendar'[DateActual] <= MAX ( 'Calendar'[DateActual] )
        )
    )
)

Like i said i am struggling with open days and due days measure calculation. Open days should show the number of days between the displayed date and the posting date. Due days should show the difference between the displayed date and due date. Negative is before due, positive is due. And the measures should both aggregate as averages.

I've checked several posts but i couldn't get any of the proposed solutions working.

Thanks in advance, any ideas would be appreciated.

[v-ljerr-msft · Accepted solution]

Hi @PowPow,

Have you tried using SUMX Function (DAX) and DATEDIFF Function (DAX) to calculate open and due days in this scenario? The formula below is for your reference.

Open Days :=
VAR maxDate =
    MAX ( 'Calendar'[DateActual] )
RETURN
    IF (
        MIN ( 'Calendar'[DateSK] )
            <= CALCULATE ( MAX ( Receivables[PostingDateSK] ); ALL ( Receivables ) );
        CALCULATE (
            SUMX ( Receivables; DATEDIFF ( maxDate; Receivables[PostingDate]; DAY ) );
            USERELATIONSHIP ( Receivables[DetailedLedgerPostingDateSK]; 'Calendar'[DateSK] );
            FILTER ( ALL ( 'Calendar'[DateActual] ); 'Calendar'[DateActual] <= maxDate )
        )
    )

Due Days :=
VAR maxDate =
    MAX ( 'Calendar'[DateActual] )
RETURN
    IF (
        MIN ( 'Calendar'[DateSK] )
            <= CALCULATE ( MAX ( Receivables[PostingDateSK] ); ALL ( Receivables ) );
        CALCULATE (
            SUMX (
                Receivables;
                IF (
                    Receivables[DueDate] > maxDate;
                    DATEDIFF ( maxDate; Receivables[DueDate]; DAY );
                    DATEDIFF ( Receivables[DueDate]; maxDate; DAY )
                )
            );
            USERELATIONSHIP ( Receivables[DetailedLedgerPostingDateSK]; 'Calendar'[DateSK] );
            FILTER ( ALL ( 'Calendar'[DateActual] ); 'Calendar'[DateActual] <= maxDate )
        )
    )

Regards

[PowPow]

Hi @v-ljerr-msft,

of course, variables! Thank you very much for this, it helps a lot! I will have to limit the calculations to just count the days when the transaction is still open/due but that should be straitghtforward.

Thanks again!

Best,