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.
Sorry for this exceedingly basic question, but I am trying to report cycle count data for our auditors. They only want to see the most recent cycle count on each item. So how do I tell PowerBI to only report lines with the most recent Posting Date for each item?
So my table would look like:
Item number Posting Date Physical Count
12071 11/22/2016 10,537
12071 11/30/2016 9.725
The table has several items that have multiple lines because of being counted more than once within the reporting time period. I want the final report to only show, in this case, the second line.
This is probably PowerBI 101, but I can't seem to find anything on the web on how to set this up and my brain is just misfiring when thinking about this one.
Thank you for any assistance you can provide.
I guess this was not as basic as I first thought. Still have not found a solution to the issue. Oh well.
Sorry for my late response. Can you zoom in your screenshot a little bit so that we can see your data clearly?
Regards,
In this scenario, you just need to limit the context into max date rows.
Measure 3 = CALCULATE(SUM(Table4[Phyiscal Count]),FILTER(Table4,Table4[Date]=MAX(Table4[Date])))
Regards,
Here is what I got when I tried that code snippet:
After Item 11908 it appeared to show the later date as 2.00 and the earlier as 1.00. Prior to 11908, it just shows 1.00 in the Measure 3 column no matter if there are mulitple entries or not.
I am so confused. 😕
Hi @srhoag
Try the following
1. Create a measure LastPostingDate = Max (yourtablename[PostingDate])
2. Now plot the [Item Number] and [LastPostingDate] and you will get the desired results.
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Hello CheenuSing,
Thank you for the code, but that did not help in this case. All I got was another column that repeated the Posting Date on each line.
What I need the report to do is only show the line with the latest posting date for each item. Some items are listed muliple times as they have been cycle counted more than once and each cycle count adds a line to the table for that item.
Maybe the screen shot helps. I may not have explained the issue clearly in the initial question.
Thank you for the response though! - Steve
This can easily be done in Power Query (Get and transform data):
group by Item and select all rows, This will give you a table with items and a column with a table with all data for that item,
Add a column selecting the maximum date from each of those item tables.
This will give you a record with the data of the most recent date.
Expand that column and choose fields Posting Date and Physical Count.
Below the code created in Excel with input table "CycleCounts".
let Source = Excel.CurrentWorkbook(){[Name="CycleCounts"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Number", Int64.Type}, {"Posting Date", type datetime}, {"Physical Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Item Number"}, {{"OtherData", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([OtherData], "Posting Date")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Posting Date", "Physical Count"}, {"Posting Date", "Physical Count"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Posting Date", type date}, {"Physical Count", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"OtherData"}) in #"Removed Columns"
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |