Need to filter a calculated measure

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, I’m in need of some help. I’ve looked and tried everything.

 

I need to filter a measure. But in a pivot table you cant filter a measure. I would like to know what items were FIRST received to the stores in the last 3-5 weeks. So meaning the first received date is in the last 3-5 weeks. Items received many times, and the first time was a year ago. But was received again 3 weeks ago would be skipped. Only looking for new items.

This is my calculation in the column to know how old the receive date is.

=if(TODAY()-[JOURNAL DATE]<7,1,if([JOURNAL DATE]=BLANK(),999,(TODAY()-[JOURNAL DATE])/7))

Then is is my measure to know the age of the first time the item was ever received.

FIRST SENT TO STORES AGED UNFILTERED:=CALCULATE(MAX(‘TRANSFERS'[FIRST SENT TO STORES]),FILTER(ALL(dCalendar[DATES]),’dCalendar'[DATES]<=MAX(‘dCalendar'[DATES])))

Thanks for reading.

 

 

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Firstly, to clarify for readers regarding “in a pivot table you can’t filter a measure”: Measures are filtered in a pivot table by content in rows, columns, filter and slicers (these four items often referred to as the “original” filter context).

Questions for you:

Do you want to know, for each store, which products were first received in the last 3-5 weeks or do you want to know which products were first received at any store in the last 3 to 5 weeks?

When you mention the “last 3 to 5 weeks” are you only interest in items first received between week 3 and week 5, OR something else?

What is the name of the column that contains the formula: “=if(TODAY()-[JOURNAL DATE]<7,1,if([JOURNAL DATE]=BLANK(),999,(TODAY()-[JOURNAL DATE])/7))"?

Is the calculated column referenced above in the Transfers table?

Does your data model have lookup tables for Items and Stores?

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Another question: Assuming the Transfers table contains information about items received in stores, does it contain other records like information shipped to stores or items returned from stores?

Or is it possible to have an event where a non-item (non-product) is received at a store (for example, uniforms for employees, cleaning-supplies for store maintenance, etc.) and such transactions are recorded in the transfers table?

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi the name of the column that contains the number of weeks on the floor is :”FIRST SENT TO STORES”

 

For now it’s not as important to know which stores it was received for. It more important to know what new products have been sent to the stores and how they have done with it.

 

Thanks, Steven

 

 

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, it only contains merchandise.

 

I have a receiving table that has the item details (model, brand…) And I would like to know just the new items that have been sent to stores so I can track how well or poor they are doing.

 

This would allow us to make more informed discussion on reorders or returns.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Is the receiving table the same as TRANSFERS?

[tomallanKeymaster Started: 0Replies: 417Total: 417]

If it were me, I would use Power Query to create a table of unique items and relate this new table to Transfers.

In the new Items table, I would create a calculated to determine the actual first date each product was sold. It would look something like this:

Item First Sales Date = MINX ( RELATEDTABLE ( Transfers ), [Journal Date] )

Additionally, I would create a calculated column in the same Items for the number of weeks from today for the first sales date and name that column something like “Weeks in Stores”

Then I would create a pivot with items on rows and create a slicer using the “Weeks in Stores” column.

At that point, I could add put any calendar unit on pivot columns or rows, and place any measure for transfers to stores inside the pivots drop zone, and based on slicer selection for “Weeks in Stores”, I could find out how items were performing.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

<span style=”color: #333333;”>For this case yes.

Items are received against the receiving table to the warehouse. And then transferred from the warehouse to each store.

So for analysis I only need to look at the transfer table and the column first sent to stores.

If he item has been sent many times I only look at MAX being the highest number under firsts sent to stores.</span>

[tomallanKeymaster Started: 0Replies: 417Total: 417]

I might be wrong how I see your data model, but right now I am picturing that you are trying to write a formula with just one table and, if so, I believe that will prove a pain point for this case.

Could you put together some sample data and post? The sample data should either be fictitious or anonymized, but your data model should be consistent with your production version.

Tom

[Author]

Posts