Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
What should be a quick question here - I have a two-column table, a date/time/zone stamp and a member count (example pics below). All I need to do is keep the last date of each month, such that the resulting table has one row for each month, and the final member count for that month. Any help?
The first few rows for April 2021:
The final few rows for April 2021. I just need a row (April 2021) with the value 22,083. This pattern continues into August.
Solved! Go to Solution.
let
Source = Cds.Entities("anonymous source", [ReorderColumns=null, UseFormattedValue=null]),
entities = Source{[Group="entities"]}[Data],
#"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
#"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "anonymous filter")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Other Columns", each ([new_membercount] <> 0)),
#"Grouped by month" = Table.Group(#"Filtered Rows2", "new_createdon", {"Grouped by Mo", each Table.Sort(_, {"new_createdon", Order.Descending}){0}}, 0, (x,y) => Number.From(Date.Month(x)<>Date.Month(y))),
Extraction = Table.FromRecords(#"Grouped by month"[Grouped by Mo])
in
Extraction
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Easy one, group the table by month and extract the desired rows.
This solution works regardless the last record falls on the end of the month.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7JCcAwDETRVoLOBm1eErdi3H8bTmKkgHKdx8AfAzKyoJDwUTu1TgQJmGCmlzSSOOVI6lQiZacaqRgpRaqRmlPbVJC3nPds7Ub2+kx+drnpY3MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [new_createdon = _t, new_membercount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"new_createdon", type datetime}, {"new_membercount", Int64.Type}}),
#"Grouped by month" = Table.Group(#"Changed Type", "new_createdon", {"Grouped by Mo", each Table.Sort(_, {"new_createdon", Order.Descending}){0}}, 0, (x,y) => Number.From(Date.Month(x)<>Date.Month(y))),
Extraction = Table.FromRecords(#"Grouped by month"[Grouped by Mo])
in
Extraction
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @CNENFRNL - this looks like what I'll need, since I still want values for the most recent date in a month that's still in progress. However, I'm not experienced enough yet to apply your code in the advanced editor to a non-static source. Given the anonymized query below, could you help me to add your code? Thanks!
let
Source = Cds.Entities("anonymous source", [ReorderColumns=null, UseFormattedValue=null]),
entities = Source{[Group="entities"]}[Data],
#"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
#"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "anonymous filter")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"new_createdon", Order.Ascending}})),
#"Filtered Rows2" = Table.SelectRows(#"Sorted Rows", each ([new_membercount] <> 0))
in
#"Filtered Rows2"
let
Source = Cds.Entities("anonymous source", [ReorderColumns=null, UseFormattedValue=null]),
entities = Source{[Group="entities"]}[Data],
#"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
#"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "anonymous filter")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Other Columns", each ([new_membercount] <> 0)),
#"Grouped by month" = Table.Group(#"Filtered Rows2", "new_createdon", {"Grouped by Mo", each Table.Sort(_, {"new_createdon", Order.Descending}){0}}, 0, (x,y) => Number.From(Date.Month(x)<>Date.Month(y))),
Extraction = Table.FromRecords(#"Grouped by month"[Grouped by Mo])
in
Extraction
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL Nifty, my Table.Group skillz don't have nearly the drip yours do...
Hey @RMDNA just use this as your filter in a Table.SelectRows statement. Filter your date by anything just to get the statement written, then replace the filter condition in the formula bar with this:
DateTime.Date([Date]) = Date.EndOfMonth(DateTime.Date([Date]))
It compares the date only of the end of the month for that date and keeps it. Full code with sample data is here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3MtQ3MjAyVDA0szIwUIrVAYkZG0DEDCxgYsb6xlB1JgghQ1OoVkuwWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Filtered Rows" =
Table.SelectRows(
#"Changed Type",
each
(DateTime.Date([Date]) = Date.EndOfMonth(DateTime.Date([Date])))
)
in
#"Filtered Rows"
If you need more help, provide sample data we can use. Cannot use images. Good for expected results, not for input.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans - this would work well if not for two things: I need the most recent data for a month still in progress (e.g. whatever the value is for today, 8/4), and this takes the last day of the month, but since it's a date/time field, there's 20 rows for 4/30.
If there's any way to adapt for this, let me know!
Hi @edhans (and also @Greg_Deckler and @CNENFRNL) - sorry, I'm juggling threads from several of you right now.
Download link for a PBIX with the data is here.
Just for clarity's sake: I need to get the value of "new_membercount" from the last date/time stamp in a month, as well as the same value for the most recent day of the current month. For example, the EndOfMonth solution that edhans originally provided gave the following:
However, I need only that last value for 6/30. Additionally, you'll see there's no data for 8/4 (today), which is the latest date of this current month.
An anonymized version of the query is as follows, so if you provide code, please let me know how it would fit into this query:
let
Source = Cds.Entities("redacted datasource", [ReorderColumns=null, UseFormattedValue=null]),
entities = Source{[Group="entities"]}[Data],
#"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
#"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "redacted filter")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"new_createdon", Order.Ascending}})),
#"Filtered Rows2" = Table.SelectRows(#"Sorted Rows", each ([new_membercount] <> 0))
in
#"Filtered Rows2"
Thanks!
Can you post some data per the links below? Very hard to type stuff in and try to replicate your scenario.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAh @edhans, I am a fool, a fool I tell you, for forgetting there is an EndOfMonth function in Power Query!!
There are over 800 functions in Power Query @Greg_Deckler - no one can possibly remember them all! 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@RMDNA Not as easy as you might think but here is one way:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5BCsAgDETRq5SsBZNoYutVxPtfo2KrLbOdx8BvjXIUjcoqh1culZkCCfUwJaHokoySlhhKXuIo9kpiFAcpW8oUi/LAOdZ/tO3LJwpybRnR/QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [new_createdon = _t, new_membercount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"new_createdon", type datetime}, {"new_membercount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Year([new_createdon]) * 100 + Date.Month([new_createdon])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Group(#"Added Custom", {"Custom"}, {{"max", each List.Max([new_createdon]), type nullable datetime}})),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"max"}, {"Custom.1.max"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [new_createdon] = [Custom.1.max] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.1] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1.max", "Custom.1"})
in
#"Removed Columns"
@ImkeF @edhans and @mahoneypat might know of a better way.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |