Forum Discussion

Frequent Visitor

9 years ago

Solved

Snapshot open and due days

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...

Need Help

v-ljerr-msft

9 years ago

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

v-ljerr-msft

Microsoft Employee

9 years ago

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

Frequent Visitor

9 years ago

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,