Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculating age of document dynamically

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

 

 

 

 

Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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:

     

     

     

     

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Just create a column or measure like:

     

    AgeOfDocument = ARHistory[Due date] - ARHistory[Document date] * 1.
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        9 years ago

        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