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

[srhoag]

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.

[srhoag]

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

[v-sihou-msft]

@srhoag

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

Regards,

[v-sihou-msft]

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

Regards,

[srhoag]

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

[CheenuSing]

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

[srhoag]

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

[MarcelBeug]

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"