Use existing measure to generate Average Days Outstanding

[MeganLouise93]

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!

[dharmendars007 · Accepted solution]

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

LinkedIN 

[mark_endicott]

@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!

[DataNinja777]

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,

[mark_endicott · Accepted solution]

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

[dharmendars007 · Accepted solution]

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

LinkedIN