Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NrAg
Frequent Visitor

Next highest Date per Category

Hi PBI-experts!

 

Recently, I faced the challenge of finding the last day of period based on the first date of the next period on a product level. Date would indicate the end-date of that specific period. Whenever it was the last entry and there would be no following the value should be left empty. To provide a very simplified sample of the data set to work with: 

 

product_namestart_dateprice
sweatshirt_fantasy_brand_012022-1-3115
sweatshirt_fantasy_brand_012022-3-2413
sweatshirt_fantasy_brand_012022-4-1712
sweatshirt_fantasy_brand_012022-7-1011
pants_fantasy_brand_012022-1-524
pants_fantasy_brand_012022-7-28

23

 

I found a solution via DAX which identifies the next highest date value for the specific product group. I used it as a calculated column in the respective table. However, as far as I know it would be more performant to create that calculation in query editor as a computed column and therefore, I wanted to ask if you know how to calculated the next highest max date within the product group, take the day before and assign it to a new column with the condition to leave it null in case there is no higher entry for that specific product. 

 

Looking forward to hearing back from you and discussing the solutions together 🙂   

3 ACCEPTED SOLUTIONS
jbwtp
Memorable Member
Memorable Member

Hi @NrAg,

 

Are you looking for something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc5BCoAgEIXhu7h2wBkVu4uITFTURkKF6PY1HSDc/Yv3wYtRtWvl3vaj9rxx6dzuPFcuSzaotCJDBAhWGr1KeghYICfAjgIHGATQKAiARgB+4HxX7ee+l3QD0wA0Sb/H0wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product_name = _t, start_date = _t, price = _t]),
    
    f = (t as table) as table =>
        let
            add = Table.ToColumns(t) & {List.Transform(List.Skip(t[start_date]), each Date.AddDays(_, -1))},
            format = Table.FromColumns(add, Value.Type(Table.AddColumn(t, "end_date", each null, type date)))
        in
            format,

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product_name", type text}, {"start_date", type date}, {"price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"product_name"}, {{"Out", f, Value.Type(Table.AddColumn(#"Changed Type", "end_date", each null, type date))}}),
    #"Expanded Out" = Table.ExpandTableColumn(#"Grouped Rows", "Out", {"start_date", "price", "end_date"}, {"start_date", "price", "end_date"})

in #"Expanded Out"

 

Cheers,

John

View solution in original post

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1673931329596.png

 

NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(Table.Sort(PreviousStepName,{"product_name",{"start_date",1}})),{{},[]},(x,y)=>{{y&[end_date=Record.FieldOrDefault(x{1},y[product_name],null)]}&x{0},Record.TransformFields(x{1},{y[product_name],each Date.AddDays(y[start_date],-1)},2)}){0})

View solution in original post

I wish I would fully understand the formula but will definitely spend some time to read it through. However, it works perfectly and I very much appreciate your help! 

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1673931329596.png

 

NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(Table.Sort(PreviousStepName,{"product_name",{"start_date",1}})),{{},[]},(x,y)=>{{y&[end_date=Record.FieldOrDefault(x{1},y[product_name],null)]}&x{0},Record.TransformFields(x{1},{y[product_name],each Date.AddDays(y[start_date],-1)},2)}){0})

I wish I would fully understand the formula but will definitely spend some time to read it through. However, it works perfectly and I very much appreciate your help! 

jbwtp
Memorable Member
Memorable Member

Hi @NrAg,

 

Are you looking for something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc5BCoAgEIXhu7h2wBkVu4uITFTURkKF6PY1HSDc/Yv3wYtRtWvl3vaj9rxx6dzuPFcuSzaotCJDBAhWGr1KeghYICfAjgIHGATQKAiARgB+4HxX7ee+l3QD0wA0Sb/H0wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product_name = _t, start_date = _t, price = _t]),
    
    f = (t as table) as table =>
        let
            add = Table.ToColumns(t) & {List.Transform(List.Skip(t[start_date]), each Date.AddDays(_, -1))},
            format = Table.FromColumns(add, Value.Type(Table.AddColumn(t, "end_date", each null, type date)))
        in
            format,

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product_name", type text}, {"start_date", type date}, {"price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"product_name"}, {{"Out", f, Value.Type(Table.AddColumn(#"Changed Type", "end_date", each null, type date))}}),
    #"Expanded Out" = Table.ExpandTableColumn(#"Grouped Rows", "Out", {"start_date", "price", "end_date"}, {"start_date", "price", "end_date"})

in #"Expanded Out"

 

Cheers,

John

NrAg
Frequent Visitor

thank you jbwtp! Your solution works as well. Unfortunately I have as much struggle to disassemble your code than I had with Daniels. But I will have a look at Query Editor Documentation. Do you have good suggestions for YT-channels or alike for improving my knowledge in M? 

jbwtp
Memorable Member
Memorable Member

Hi @NrAg,

 

Try this series: Power Query M Primer (Part 14): Control Structure | Ben Gribaudo

YT or other platform-wise: it is probably better to search for a solution to a specific problem - this way I learn M. For some general topics around PBI, this one can be usefull: https://www.youtube.com/@CurbalEN

Good source of conceptual knowledge may be Chriss Web's blog (search for Chris crossjoin). Early records are most usefull for starters.

 

Cheers,

John

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors