Dynamic Date Difference Measure

[sjrrkb123]

Here is my situation:

I have a dataset of invoices and cash receipts. Each invoice has a single due date, each cash receipt has a cash received date, and each invoice has an invoice date. I have created a measure that dynamically calculates the days past due based on the calendar (from a date table) dates shown and an invoice due date. 

Here is what the data looks like:

Invoice		Date		Payment Type		Payment		Due Date
1A1		1/1/2020		Invoice		1000		1/3/2020
1A1		1/4/2020		Cash Received		-500		1/3/2020
1A1		1/5/2020		Cash Received		-500		1/3/2020
1B1		2/4/2020		Invoice		2000		2/7/2020
1B1		2/5/2020		Cash Received		-1500		2/7/2020
1B1		2/20/2020		Cash Received		-250		2/7/2020
1B1		2/21/2020		Cash Received		-200		2/7/2020
1B1		3/1/2020		Cash Received		-50		2/7/2020

I have a date table connected to the Date Column as an active relationship and another connection based on the due date that is inactive.

Note, the screenshots below are the calculations used on the actual data while the table above is dummy data. One can substitute [Due Date] for invoice due date.

Here are the calculations that I use for the dynamic date difference:

Then I run that over all dates in this calculation:

This calculation works great when only 1 invoice is chosen. The problem is when I select more than 1 invoice, it is still using the due date from the first invoice. I know the problem stems from the Dynamic Aging Days - Temp calculation, specifically the MIN('Historical Aging Dataset' [Invoice Due Date]). What I do not know how to do is to use DAX to force Power BI to use due dates for each invoice rather than the earlier due date selected from a set of invoices. 

I have tried using these for the due date calculation but none have worked.

• Calculate(MIN('Historical Aging Dataset' [Invoice Due Date]), ALLSELECTED('Historical Aging Dataset'[Invoice]) 
• Calculate(MIN('Historical Aging Dataset' [Invoice Due Date]), ALLSELECTED('Historical Aging Dataset') 

Any ideas on what to do would be very appreciated. This is literally the last thing I need for this aging data power bi report. 

[v-easonf-msft]

Hi， @sjrrkb123 

As mentioned by @lbendlin ,try measure as below:

Calculate(MIN('Historical Aging Dataset' [Invoice Due Date]), ALLExcept('Historical Aging Dataset','Historical Aging Dataset'[Invoice]) 

If the function does not work, please share your pbix file and expected results for testing.

Best Regards,
Community Support Team _ Eason

[sjrrkb123]

Unfortunately, this did not work. It still works great for a single invoice but getting more than 1 does not yield the correct results. 

For example, using invoice 1A1 (from fake data), there are 3 events

and when only selected, the aging days calculation looks as follows:

However, when looking at it through the lens of two invoices, 1A1 & 1B1, then things break down.

Here is the information for both invoices:

Unfortunately, the dynamic aging days calculation does not take this into account as can be seen below ere there are no Current Aging values on 2/4/2020 and after.

As it stands now, I am using the following calculations for aging days and then for bucketing them in the appropriate aging group;

Dynamic Aging Days - Temp:

Dynamic Aging Days - Temp =

var due_date = CALCULATE(MIN('Current State'[Due Date]), ALLEXCEPT('Current State', 'Current State'[Invoice]))

var cal_date = MAX('Dates - Dim Dates'[Calendar Date])

RETURN

CALCULATE(

DATEDIFF(due_date, cal_date, DAY),

ALLSELECTED('Dates - Dim Dates'),

'Dates - Dim Dates'[Calendar Date] <= cal_date,

USERELATIONSHIP('Current State'[Due Date], 'Dates - Dim Dates'[Calendar Date]))

Dynamic Aging Days:

Dynamic Aging Days =

var max_date = MAX('Dates - Dim Dates'[Calendar Date])

RETURN

CALCULATE([Dynamic Aging Days - Temp],

ALLSELECTED('Dates - Dim Dates'),

'Dates - Dim Dates'[Calendar Date]<= Max_date)

Running Balance:

Running Balance =

var max_date =MAX('Dates - Dim Dates'[Calendar Date])

var slns =

CALCULATE(SUM('Current State'[Payment]),

ALLSELECTED('Dates - Dim Dates'[Calendar Date]),

'Dates - Dim Dates'[Calendar Date] <= max_date)

RETURN

slns

Current Aging:

Current Aging = IF([Dynamic Aging Days] < 1, [Running Balance])

1-30 Days Past Due:

1-30 Days Past Due = IF([Dynamic Aging Days]>0 && [Dynamic Aging Days] <31, [Running Balance])

The goal of this is to be able to dynamically calculate aging days for any number of invoices selected. The dummy dataset I will post after this only has 2 invoices (if it can work for n +1, I figure it can work for any n number of invoices). Then using the dynamic aging days, bucket the running balance of a given invoice into the appropriate aging days bucket (Current, 1-30 days past due, 31-60 Days Past due, 60 + days past due, etc..)

This will be used in a stacked column chart with no legend but using each measure as the specific aging bucket. 

[sjrrkb123]

Here is the Power BI link:
https://drive.google.com/file/d/1-UoU12e7n48ceZAEG88nmw8T_3SLsjsJ/view?usp=sharing 

[lbendlin]

Use ALLEXCEPT(invoicenumber)