Calculating receivable aging dynamically

[voidbydefault]

Hi,

I want to calculate "aged balances" dynamically in a way that dashboard users have two options to change the overdue days being overdue days as option 1) [Invoice Due Date] and TODAY(); and 2) [Invoice Due Date] and [Report Date].

Step 1: To achieve this purpose, I created a data table using DAX:

```

AgingCutoffSelection =

DATATABLE(

    "Cutoff", STRING,

    {

        {"Today"},

        {"Report Date"}

    }

)
```

Step 2: Create a measure in a table bi_calcs and add to a slicer to enable user to switch:

 ```

Aging Cutoff Date =

SWITCH(

    SELECTEDVALUE(AgingCutoffSelection[Cutoff]),

    "Report Date", SELECTEDVALUE(data_006_custBal[Report Date]),

    "Today", TODAY()

)

```

Step 3: In customer balances table, add a column using DAX to add "xDays Outstanding"

```

xDays Outstanding = DATEDIFF(data_006_custBal[Due Date], [Aging Cutoff Date], DAY)

```

I am facing problem in step 3 that the output is coming as blank. I have checked the output of step 1 and 2 is as expected.

Any help to fix? Thanks.

NB: Can't share PBIX, it has organizational data.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from ajohnso2 and johnt75 , please allow me to provide another insight:

Hi, @voidbydefault 
 

Could you please let me know if the responses from ajohnso2 and johnt75 have resolved your issue? If they have, kindly accept their answers as the solution

I share the same views as ajohnso2 and johnt75, and I’ve created examples based on their perspectives to aid your understanding. Below are my sample data:

1.First, ajohnso2's proposal:

This modifies the final step of your approach by adjusting the measures:

xDays Outstanding = DATEDIFF(MAX('data_006_custBal'[Invoice Due Date]), [Aging Cutoff Date], DAY)

Here are the final results:

2.Second,  johnt75 's proposal:

Firstly, create the following two measures:

Measure 1 = DATEDIFF(MAX('data_006_custBal'[Invoice Due Date]),MAX('data_006_custBal'[Report Date]),DAY)

Measure 2 = DATEDIFF(MAX('data_006_custBal'[Invoice Due Date]),TODAY(),DAY)

Then, create the parameters:

Rename them accordingly and create the slicer:

Parameter = {
    ("Report Date", NAMEOF('data_006_custBal'[Measure 1]), 0),
    ("Today", NAMEOF('data_006_custBal'[Measure 2]), 1)
}

Here are the final results:

I hope my explanations help you grasp their proposals. Both solutions are excellent ideas, and I trust you will find them useful.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

[voidbydefault · Accepted solution]

First, thanks to @johnt75 , @ajohnso2 , and @v-linyulu-msft for your time in trying to help. In my unique case, I realized I need to calculate invoice aging at row level so I added four columns in my data table as:

Then created a parameter with its slicer:

test_OS_Days = {
    ("xDays Outstanding from Report Date", NAMEOF('data_006_custBal'[xAging Bucket Report Days]), 0),
    ("xDays Outstanding from Today", NAMEOF('data_006_custBal'[xAging Bucket Today Days]), 1)
}

This enabled the functionality I was aiming to achieve. 

[voidbydefault]

Many thanks, Leroy. Their tips were helpful, but your explanation plus demo nailed it, so I accepted yours as the solution.

One question, though: is it important to have all the measures, like `Measure 1`, `Measure 2`, `xDays Outstanding`, and `Aging Cutoff Date`, in table `data_006_custBal`? I usually prefer to have measures separated in a table created using DAX called "BI_calcs" to keep my calculations separate from data tables.

Thanks again.

Edit 1: One observation is that the solution is only working if measures can see details like invoice due date to have a context to do the calculations. If I remove details like customer ID and invoice date, then "xDays Outstanding" goes bonkers. One of my aims is to have a visual showing days grouped into buckets like 0-90, etc., with balances only, so this is an emerging issue to solve. 

Edit 2: "xDays Outstanding" is unusable in bar chart visualization as x-axis. The second proposal using parameters works, but the outcome is affected by issue mentioned in "Edit 1" above.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from ajohnso2 and johnt75 , please allow me to provide another insight:

Hi, @voidbydefault 
 

Could you please let me know if the responses from ajohnso2 and johnt75 have resolved your issue? If they have, kindly accept their answers as the solution

I share the same views as ajohnso2 and johnt75, and I’ve created examples based on their perspectives to aid your understanding. Below are my sample data:

1.First, ajohnso2's proposal:

This modifies the final step of your approach by adjusting the measures:

xDays Outstanding = DATEDIFF(MAX('data_006_custBal'[Invoice Due Date]), [Aging Cutoff Date], DAY)

Here are the final results:

2.Second,  johnt75 's proposal:

Firstly, create the following two measures:

Measure 1 = DATEDIFF(MAX('data_006_custBal'[Invoice Due Date]),MAX('data_006_custBal'[Report Date]),DAY)

Measure 2 = DATEDIFF(MAX('data_006_custBal'[Invoice Due Date]),TODAY(),DAY)

Then, create the parameters:

Rename them accordingly and create the slicer:

Parameter = {
    ("Report Date", NAMEOF('data_006_custBal'[Measure 1]), 0),
    ("Today", NAMEOF('data_006_custBal'[Measure 2]), 1)
}

Here are the final results:

I hope my explanations help you grasp their proposals. Both solutions are excellent ideas, and I trust you will find them useful.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

[ajohnso2]

I think the issue your facing is due to the procedence of element evaluations, e.g. Calculated columns first and then measures, so with that in mind Aging Cutoff Date is not yet evaluated to complete the calculation of xDays Outstanding.

Rewrite it as a measure 

xDays Outstanding = DATEDIFF(MAX(data_006_custBal[Due Date]), [Aging Cutoff Date], DAY)

[johnt75]

A calculated column is only calculated during data refresh, it ignores any filters or slicers. If you want something to react dynamically you would need to do it as a measure.

Another solution which might work for your use case is to create 2 measures, one for each cut off date, and then create a field parameter with these 2 measures, allowing the user to switch between them.