cancel
Showing results for
Did you mean:
Highlighted
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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
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
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

Frequent Visitor

## Re: Snapshot open and due days

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,

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 296 members 3,196 guests
Recent signins: