Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
Just create a column or measure like:
AgeOfDocument = ARHistory[Due date] - ARHistory[Document date] * 1.
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
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
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
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |