Calculate Max date in a table with filters on multiple columns, to find distinct dates for each row.

[Jakemh22]

Hi There,

I'm new to the Forum world and thought this might be a good place to post a query.

I have been trying to add a calculated column into a table, which returns the MAX date found, against multiple filtered column.

Example of a table;

Posting Date         Document Type      Source Type        Document No.      Customer No.     

28/03/2023                      2                           0                    SI-048532                 3504

22/03/2023                      1                           2                    SI-098321                 3504

22/03/2023                      3                           2                    SI-026831                 3504

08/03/2023                      15                         2                    SI-048772                 3504

So, i can build a measure which finds my max date using the below;

LAST INV DATE = CALCULATE(MAX('WadworthLive$Detailed Cust_ Ledg_ Entry'[Posting Date].[Date]),FILTER('WadworthLive$Detailed Cust_ Ledg_ Entry','WadworthLive$Detailed Cust_ Ledg_ Entry'[Source Type]=0&&'WadworthLive$Detailed Cust_ Ledg_ Entry'[Document Type]=2))

This will return the correct date, however if i put this into a column in the table itself, the max date for the entire table is found instead of being specific to the Customer No. in the row. 

This is where i become stuck.

I would appreciate any support or guidance.

Regards,

Jake.

[Jakemh22]

Good morning,

Thank you both for some really good solutions to my query.

This has helped me pull some fantastic dashboards together which will really help drive our business forward!

Good luck to both of you, i really appeaciate the support.

look forward to speaking with you soon.

best wishes,

Jake.

Best Wishes,

Jake Harkness

[lbendlin]

You cannot create a calculated column from a measure.  Find another way to solve your requirement.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Jakemh22]

Good Morning,

Thankyou for coming back to me.

Here is a sample from some of the data i have in a 'table format' with the expected outcome column 'Last Posting Date'.

Posting Date	Document Type	Source Type	Document No.	Customer No.	Last Posting Date
28/03/2023	15	2	SI-048532	3504	08/03/2023
22/03/2023	1	2	SI-098321	3504	08/03/2023
22/03/2023	3	2	SI-026831	3504	08/03/2023
08/03/2023	2	0	SI-048772	3504	08/03/2023
02/03/2023	15	2	SI-038992	3480	26/03/2023
16/03/2023	1	2	SI-034562	3480	26/03/2023
21/03/2023	3	2	SI-043172	3480	26/03/2023
26/03/2023	2	0	SI-038173	3480	26/03/2023

As i mentioned in my first comment, i can write a measure to find the last Posting date in this table, where Document Type = 2 && Source Type = 0. However, this restricts me with certain report funcionality using a measure instead of a calculated column.

The reason i would find it more beneficial having a calculated colum within the table,  is because the table has a relationship to an Item table, which has the Items relating to each Document No.  

So, when i use a measure to retrieve the last posting date, there is no relationship to be able to retrieve the items associated with the document no. unless the field is a calculated column itself. 

I'm sure there are many other ways i can achieve my goal but this is all learning, and i'd be interested to know how to achieve the above anyway, hope that makes sense.

Thanks,

Jake.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Last posting date = CALCULATE(MAX(Data[Posting Date]),FILTER(Data,Data[Customer No.]=EARLIER(Data[Customer No.])&&Data[Document Type]=2&&Data[Source Type]=0))

Hope this helps.

[lbendlin]

You don't need a calculated column for that, it can be done in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc4xDoAgDIXhuzBrbN8rUI7g7Ei8/zVEBwNDBxqGL3l/7wl+CA8ImLakeRyMd527mGe+f2axdG+DYqaTbE5oKDlJFOciZZ5/lfzrtS7rgiiU3tpHzeWjWoJQWi6LhAahRq2rLEEoXSt/eT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Document Type" = _t, #"Source Type" = _t, #"Document No." = _t, #"Customer No." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer No."}, {{"Last Posting Date", each List.Max([Posting Date]), type nullable date}, {"Rows", each _, type table [Posting Date=nullable date, Document Type=nullable text, Source Type=nullable text, #"Document No."=nullable text, #"Customer No."=nullable text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Posting Date", "Document Type", "Source Type", "Document No."}, {"Posting Date", "Document Type", "Source Type", "Document No."}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Posting Date", "Document Type", "Source Type", "Document No.", "Customer No.", "Last Posting Date"})
in
    #"Reordered Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".