Calculating age of document dynamically

[Erwin]

Hi all,

I've got two tables, ARHistory and DocumentDates, the first being my fact table and the second being my calendar table. These tables are related through 'ARHistory'[Document date] -> 'DocumentDates'[DateKey].

I want to to write a measure [AgeOfDocument], which should calculate the difference between 'ARHistory'[Due date] and 'DocumentDates'[DateKey]. This is what I got so far:

AgeOfDocument:=
CALCULATE (
    COUNTROWS ( DocumentDates ),
    ALL ( DocumentDates[DateKey] ),
    DATESBETWEEN (
        DocumentDates[DateKey],
        MAX ( ARHistory[Due date] ),
        MAX ( DocumentDates[DateKey] )
    )
)

Obviously [AgeOfDocument] should give me the number of days between the selected date from 'DocumentDates'[DateKey] and each value of 'ARHistory'[Due date]. Unfortunately the above formula is giving me a fixed number of days for each 'ARHistory'[Due date].

Can you help me with this one?

Many thanks in advance!

Rg. Erwin

[Greg_Deckler]

Just create a column or measure like:

AgeOfDocument = ARHistory[Due date] - ARHistory[Document date] * 1.

[Erwin]

I'm sorry, but that's not going to do it. I need this measure to evaluate each individual value in 'ARHistory'[Due date] against the selected 'DocumentDates'[DateKey]. A calculated column will not do this as it will evaluate against a one (fixed) value within 'DocumentDates'[DateKey]. Also, using your proposed formula as a measure will not work, as it gives ambiguous results.

My idea was to count the rows for a set of dates, filtered by the DATESBETWEEN function. Unfortunately, somehow the above formula evaluates only on 'DocumentDates'. The result is always the row count of the MAX of 'DocumentDates'[DateKey] and 'DocumentDates'[DateKey].

Rg. Erwin

[Greg_Deckler]

OK, so let me parrot this back to make sure I understand. You want to select some date out of the calendar in a slicer or something, like:

May 24th, 2017

Then you want to have a measure that calculates the days aging for books with due dates like:

May 12th, 2017

May 15th, 2017

May 20th, 2017

May 24th, 2017

May 30th, 2017

The expected results would be:

-12

-9

-4

0

6

[Erwin]

Exactly!! And if I select another date in the slicer, for example:

May 20th, 2017

I want the same measure to calculate the days aging as:

-8

-5

0

4

10

Rg. Erwin

[Greg_Deckler · Accepted solution]

I think you need to get rid of your relationship or have a separate calendar table and do this:

DaysAging = (MAX([DueDate]) - MAX('Calendar'[Date])) * 1.

This was a disconnected calendar table and an enter data query like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xC8IwEAXgv3JkLrRNLc7Swak4GHQIHc54bYJnAmnAv29okDoe7+O901rcMQL6J4whEuRDVKKvW1nLpj2KqdLiMsPoDG1o4GBeayH9Tk6gkAnCDOoT4PpGZrg5ZlyoYNn84wGTsRTBeUg2j1rCVNhhZyonZ+cXio9ImP9Dv5nuVzV9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Book = _t, DueDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Book", type text}, {"DueDate", type date}})
in
    #"Changed Type"

And: