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 I'm trying to calculate based on the identification code and the date of departure, the length of time between the next previous departure date, so I can filter only those that are greater than or equal to 7 days.
I've tried these formulas, but I only get an out-of-memory error. Is there a more efficient way to do this please.
PreviousDateCalculation = CALCULATE(max('TU Feed'[departure]),FILTER('TU Feed','TU Feed'[ID]'EARLIER('TU Feed'[ID])&&'TU Feed'[departure]<EARLIER('TU Feed'[departure])))
DateDiffCalculation = DATEDIFF('TU Feed'[PreviousDateCalculation],'TU Feed'[departure],DAY)
Something doesn't appear correct. See the addition of the = sign.
PreviousDateCalculation =
CALCULATE(
max('TU Feed'[departure]),
FILTER(
'TU Feed',
'TU Feed'[ID]' = EARLIER('TU Feed'[ID]) &&
'TU Feed'[departure] < EARLIER('TU Feed'[departure])
)
)
Thankyou that now works , is it possible to have this work in reverse format so the row will show the date diff from the next date
so if I had a departure on 01/04/2020 for ID 001 and I then had a next departure on 18/04/2020 for ID 001 the row with
ID Departure Next Date Diff
001 01/04/2020 18/04/2020 18
Well, if you had all of that in a row in a table, you could just do:
Column = ([Next Date] - [Departure]) * 1.
Not sure of how your data is organized though.
I have about 8 million rows of data , and 45,000 different ID's , so it's format is a bit all over the palce , which was why I was trying to create a formula
It is a visual of how I would like it to look.
I have columns in a table called TU Feed
ID
departure date
There are 8 million rows of data with 25,000 different ID's
each row will have an ID and it's departure date.
I am looking to show all instances where there is 7 or more days before the next departure date for each ID
So I was looking to add a column with next date , and another column with the date diff.
I can then filter my visual to only show date diffs of 7 or more
OK, well, if ID is a column from a table and you have a Departure measure and Next Date measure, the formula would be the same essentially:
Measure = ([Next Date] - [Departure]) * 1.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |