cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Find most current date an item was cycle counted...

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.

7 REPLIES 7
Highlighted
Community Champion
Community Champion

Re: Find most current date an item was cycle counted...

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"

 

Specializing in Power Query Formula Language (M)
Highlighted
Super User I
Super User I

Re: Find most current date an item was cycle counted...

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Microsoft
Microsoft

Re: Find most current date an item was cycle counted...

@srhoag

 

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])))

5.PNG

 

 

Regards,

Highlighted
Regular Visitor

Re: Find most current date an item was cycle counted...

Here is what I got when I tried that code snippet:screenshot004.jpg

 

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.  😕

Highlighted
Regular Visitor

Re: Find most current date an item was cycle counted...

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.

 

screenshot005.png

 

Maybe the screen shot helps.   I may not have explained the issue clearly in the initial question.

 

Thank you for the response though! - Steve

Highlighted
Regular Visitor

Re: Find most current date an item was cycle counted...

I guess this was not as basic as I first thought.   Still have not found a solution to the issue.  Oh well.

Highlighted
Microsoft
Microsoft

Re: Find most current date an item was cycle counted...

@srhoag

 

Sorry for my late response. Can you zoom in your screenshot a little bit so that we can see your data clearly?

 

Regards,

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors