cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmilicic
Frequent Visitor

Get value from first period applied on every other period

Hello Comunity,

 

I have a table and I am trying to get first value of the column "Period_Up_Today", but I can not reach it.

 

mmilicic_0-1656832881465.png

 

If anyone could help I will really appriciate it.

 

Thanks,

Mario

 

1 ACCEPTED SOLUTION
tomfox
Super User
Super User

Hi @mmilicic ,

 

Here a solution in DAX with a calculated column:

tomfox_0-1656834693265.png

 

Here the DAX code:

Column = 
VAR _earliestDate = CALCULATE ( MINX ( ALL ( Table ), Table[Period_Up_Today] ) )
RETURN
CALCULATE ( MINX ( ALL ( Table ), Table[Amount] ), Table[Period_Up_Today] = _earliestDate ) 

 

Let me know if this helps, or if you'd like a solution in Power Query 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

View solution in original post

4 REPLIES 4
tomfox
Super User
Super User

Hi @mmilicic ,

 

How about this :

tomfox_0-1656839123523.png

 

 

Here the M that you can paste into the advanced editor:

tomfox_1-1656839186150.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcyxDcAgDETRXVyDdDaQQMkciP3X4JwiHe5+8fTXkjmnJDGYZvS/BksB2ekiFF9FQlklFMaqd2EZ/mih8McTisJ6Q1FZPRSNNVzsAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Constant Period 1" = _t, Period_Up_Today = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Constant Period 1", type date}, {"Period_Up_Today", type date}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MinDate", each List.Min([Period_Up_Today]), type nullable date}, {"AllRows", each _, type table [ID=nullable text, Constant Period 1=nullable date, Period_Up_Today=nullable date, Amount=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Period_Up_Today", "Amount"}, {"AllRows.Period_Up_Today", "AllRows.Amount"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "FilterColumn", each if [MinDate] = [AllRows.Period_Up_Today] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([FilterColumn] = 1)),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Filtered Rows", {"ID"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"AllRows.Amount"}, {"Filtered Rows.AllRows.Amount"})
in
    #"Expanded Filtered Rows"

Does this fix your issue? 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

tomfox
Super User
Super User

Hi @mmilicic ,

 

Here a solution in DAX with a calculated column:

tomfox_0-1656834693265.png

 

Here the DAX code:

Column = 
VAR _earliestDate = CALCULATE ( MINX ( ALL ( Table ), Table[Period_Up_Today] ) )
RETURN
CALCULATE ( MINX ( ALL ( Table ), Table[Amount] ), Table[Period_Up_Today] = _earliestDate ) 

 

Let me know if this helps, or if you'd like a solution in Power Query 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

mmilicic
Frequent Visitor

All good, this works good.

Thanks 😄

mmilicic
Frequent Visitor

Hi Tom,

 

Thanks 😄

 

Can you please show me the solution in Query?

I have a big dataset so maybe I can not get the result because of that.

 

All the best,

Mario

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors