cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PowPow Frequent Visitor
Frequent Visitor

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

AR.PNG

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

snapshot.PNGsnapshot table

And this is the transactional table for that same document:

transactional.PNGtransactional

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

model.PNG

 

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Snapshot open and due days

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

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Snapshot open and due days

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
Frequent Visitor

Re: Snapshot open and due days

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,

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 296 members 3,196 guests
Please welcome our newest community members: