Need Help Writing a Dated Difference Formula

[KarlConstruct]

I have a txt file brought into PowerBI via a sharepoint folder, and its a list of transmitted documents and the dates in which each transmission happened.  I don't necessarily care about all of the correspondence that happened back and forth with these documents. I only care about the earliest date and the latest date, so i can come up with a dated difference. 

Below is an example of the type of data i'm working with. The rows colored in orange and red are seperate packages (i can tell that by the description/submittal ID & the Sequence column). There are dates inbetween the first and last date, but I am unsure how to ignore the data in the middle of the first and last dates. 

Any help/advice would be appreciated. Thanks! 

[v-lid-msft]

Hi @KarlConstruct ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[KarlConstruct]

Hello, 

I incorporated your formulas into my advanced editor and then i changed some fields to match my column names, but i am recieving some errors. Please see screenshots below. 

Any help would be much appreciated. 

Advanced editor (source removed)

Submittal ID column was not found. 

However its not being found

However it is a column in the data. 

Submittal ID is a column

Overall test recieves and error as well. 

Any idea what i'm doing wrong?  Please let me know if I need to provide anything else to troubleshoot this issue im having. 

Thanks again. 

[KarlConstruct]

update to the advanced editor, still recieving the errors. 

[danextian]

Could you check on this?

n = [Submittal ID],
t = Table.SelectRows(#"Changed Type", each [Submittal ID] = n),

n references [Submittal ID] and [Submittal ID] references n.

[KarlConstruct]

I made both of the chanages mentioned above. This is what I have currently, with the following errors.  Please see the photos below. 

[v-lid-msft]

Hi @KarlConstruct ,

Sorry for our mistake, please try to use the follwing sub query, also very appreciate @danextian  can point the error.

Test = Table.SelectRows(
    #"Changed Type2",
    each let d = [Sent Date],
             n = [Submittal ID],
             t = Table.SelectRows(#"Changed Type2", each [Submittal ID]=n),
                 isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d)),
                 isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d))
         in 
             isMin or isMax
)

We do not need to change the type of Submittal ID column, the Value is the column name of record.

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc65DcAgEADBXi4G6R7eWhD9t2HLThCbTjRriUkSV5tZLavLThcFqZAqqX3kJ3XSIE2QKclI/z5OClIhVVIjddK73w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Submittal ID" = _t, #"Sent Date" = _t]),
    #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Submittal ID", type number}, {"Sent Date", type date}}),
    Test = Table.SelectRows(
    #"Changed Type2",
    each let d = [Sent Date],
             n = [Submittal ID],
             t = Table.SelectRows(#"Changed Type2", each [Submittal ID]=n),
                 isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d)),
                 isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d))
         in 
             isMin or isMax
)
in
    Test

Best regards,

[KarlConstruct]

I still seem to be getting the same errors as before unfortunately. 

I have created a pbix file with all of my code in the advance editor, if you could please take a look. I would really appreciate it. 

Dated Diff pbix 

[v-lid-msft]

Hi @KarlConstruct ,

Sorry for my mistake, we found that error is in #"Changed Type2" step, it should call the previous step name, in our sample is Source, but in yours in #"Changed Type1"

    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Submittal ID", type number}, {"Sent Date", type date}}),

ALL the queries are here ( we delete some information)

let
    Source = Excel.Workbook(File.Contents("File_Path"), null, true),
    #"Submittal Query (2)_Sheet" = Source{[Item="Submittal Query (2)",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Submittal Query (2)_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submittal Package .", type any}, {"Author Contact ID", type text}, {"Description", type text}, {"Submittal ID", Int64.Type}, {"Prolog Sequence .", Int64.Type}, {"Prolog Spec .", type any}, {"Prolog Revision .", Int64.Type}, {"Is Closed", Int64.Type}, {"Sent Date", type datetime}, {"From Contact ID", type text}, {"To Contact ID", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Sent Date", type text}}, "en-US"), "Sent Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Sent Date.1", "Sent Date.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Sent Date.1", "Sent Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Submittal ID", type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Submittal ID", type number}, {"Sent Date", type date}}),
    Test = Table.SelectRows(
    #"Changed Type2",
    each let d = [Sent Date],
             n = [Submittal ID],
             t = Table.SelectRows(#"Changed Type2", each [Submittal ID]=n),
                 isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d)),
                 isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d))
         in 
             isMin or isMax
)
in
    Test

We noticed that the Sent Date used to be a datetime value and then  you split it into date and time columns in your queries, if you want to keep the origin Sent DateColumn as datetime, we can change the query,

let
    Source = Excel.Workbook(File.Contents("File_Path"), null, true),
    #"Submittal Query (2)_Sheet" = Source{[Item="Submittal Query (2)",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Submittal Query (2)_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submittal Package .", type any}, {"Author Contact ID", type text}, {"Description", type text}, {"Submittal ID", Int64.Type}, {"Prolog Sequence .", Int64.Type}, {"Prolog Spec .", type any}, {"Prolog Revision .", Int64.Type}, {"Is Closed", Int64.Type}, {"Sent Date", type datetime}, {"From Contact ID", type text}, {"To Contact ID", type text}})
    Test = Table.SelectRows(
    #"Changed Type",
    each let d = [Sent Date],
             n = [Submittal ID],
             t = Table.SelectRows(#"Changed Type2", each [Submittal ID]=n),
                 isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),DateTime.ToText(d)),
                 isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),DateTime.ToText(d))
         in 
             isMin or isMax
)
in
    Test

Best regards,

[v-lid-msft]

Hi @KarlConstruct ,

we can try to change the "Date" to "Sent Date" as following picture to fix this problem.

Best regards,

[v-lid-msft]

Hi @KarlConstruct ,

We can filter the rows of first date and the last date in power query editor if you need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAgDEDBXVyDZPMNZcIYiP3XSAQNymuvujHkFidBrXk1r0Gm+1EkJVImlUXPSZV0kRrIlGSkve8nRVIiZVIhVdK3ny8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    Test = Table.SelectRows(
                #"Changed Type",
                each let d = [Date],
                         n = [Name],
                         t = Table.SelectRows(#"Changed Type", each [Name]=n),
                         isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Date"),"Date")),"Value"),{{"Value",type text}})),Date.ToText(d)),
                         isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Date"),"Date")),"Value"),{{"Value",type text}})),Date.ToText(d))
                      in 
                        isMin or isMax
            )
in
    Test

The formula may need to do some change based on the type and name of columns.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

You can created calculated columns like these:

Earliest Date =
CALCULATE ( MIN ( Table[Sent Date] ), ALLEXCEPT ( Table, Table[Filename] ) )

Latest Date =
CALCULATE ( MAX ( Table[Sent Date] ), ALLEXCEPT ( Table, Table[Filename] ) )

And then create another column to get the difference.

[amitchandak]

refer

https://community.powerbi.com/t5/Desktop/Using-earliest-and-latest-dates/td-p/574672

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601