topic Re: Use existing measure to generate Average Days Outstanding in DAX Commands and Tips

Use existing measure to generate Average Days Outstanding

MeganLouise93 — Wed, 18 Dec 2024 11:29:03 GMT

Hi there,

We currently have the following measure in our data model, which I'm hoping to use to build a measure for average days outstanding;

Time to Today =

CALCULATE(

DATEDIFF(

SELECTEDVALUE('Fact Finance'[Document Date]),

TODAY(),

DAY

USERELATIONSHIP('Fact Finance'[Document Date],'Dim Date'[Date])

)

Can anybody advise if/where in this I can add an average function?

Thanks!

Re: Use existing measure to generate Average Days Outstanding

dharmendars007 — Wed, 18 Dec 2024 13:32:34 GMT

Hello MeganLouise93 ,

To calculate the average days outstanding, you need to take the current logic that calculates the days difference and aggregate it using the AVERAGEX function. This allows you to average the days difference across a table.

Average Days Outstanding =
AVERAGEX('Fact Finance',
CALCULATE(
DATEDIFF(
'Fact Finance'[Document Date],TODAY(),DAY),
USERELATIONSHIP('Fact Finance'[Document Date], 'Dim Date'[Date])))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

Re: Use existing measure to generate Average Days Outstanding

mark_endicott — Wed, 18 Dec 2024 13:44:24 GMT

MeganLouise93

AVERAGEX('Fact Finance', [Time to Today] )

This will iterate through your 'Fact Finance' table and perform the [Time to Today] calculation at the row level. If you wanted to make this calculation more effiecient, you can place this calculation at a different level by providing it a particular attribute.

For example:

AVERAGEX( VALUES( 'Table'[Column] ), [Time to Today] )

Will perform the calculation over less rows, providing there are some none unique rows in the column you use in the VALUES( ), it will also provide a more specific average.

Re: Use existing measure to generate Average Days Outstanding

DataNinja777 — Wed, 18 Dec 2024 14:00:50 GMT

Hi MeganLouise93 ,

To calculate the average days outstanding using the existing measure, you can wrap it with an aggregation function like AVERAGEX, which iterates over the relevant context. The modified measure would look like this:

Average Days Outstanding = AVERAGEX( 'Fact Finance', CALCULATE( DATEDIFF( SELECTEDVALUE('Fact Finance'[Document Date]), TODAY(), DAY ), USERELATIONSHIP('Fact Finance'[Document Date], 'Dim Date'[Date]) ) )

In this measure, AVERAGEX is used to iterate over the rows of the 'Fact Finance' table and calculate the Time to Today value for each row. The CALCULATE function ensures that the USERELATIONSHIP between Document Date and the Dim Date is applied correctly. The result of these calculations is then averaged by AVERAGEX.

This approach ensures that the average is computed based on the context provided by the 'Fact Finance' table. If you need to restrict the calculation to specific rows, such as unpaid invoices, you can add additional filters within the CALCULATE function.

If you would like the analysis to align with the financially reported accounts receivable figure, I recommend using "Posting Date" instead of "Document Date."

Best regards,

Re: Use existing measure to generate Average Days Outstanding

mark_endicott — Mon, 23 Dec 2024 10:50:48 GMT

MeganLouise93 - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!