Forum Discussion
Earliest StartDate - Latest EndDate
Dear community members,
Any help is welcome, because I have no idea how to make the correct formula. I have the following question:
I have a table with StartDate, EndDate and Clientnumber and each row is a 'invoicing period'. Link to PBIX is here https://we.tl/t-kOuWC5mTqm
Now I want to know the overall invoicing period per Clientnumber, so earliest StartdDate and latest EndDate.
But how should I visualise this in a measure... Is it also possible to use this measure in a new calculated column? (I want to make a new table where each row contains an unique clientnumber, total costs etc...)
Hope to hear soon from you...
Best regards, Sander
Anonymous Well, you could put the customer in a table visual and use measures like:
MaxDate = MAX('Table'[EndDate])
MinDate = MIN('Table'[StartDate])
You could do similar things in a column but would want to do something like:
MaxDate = MAXX(FILTER('Table',[Client]=EARLIER('Table'[Client]),[EndDate])
If you want a new table, you would use similar formulas in a SUMMARIZE or GROUPBY statement.
2 Replies
Anonymous Well, you could put the customer in a table visual and use measures like:
MaxDate = MAX('Table'[EndDate])
MinDate = MIN('Table'[StartDate])
You could do similar things in a column but would want to do something like:
MaxDate = MAXX(FILTER('Table',[Client]=EARLIER('Table'[Client]),[EndDate])
If you want a new table, you would use similar formulas in a SUMMARIZE or GROUPBY statement.
Greg_Deckler Thanks for your support! The first part of your solution was just the little push I needed! Cheers, Sander