Using Time intelligence measure to filter data

[psteps]

Hi,

I have data table in PowerPivot that includes list of thousands of different retail items, their product categories, the date they were purchased, selling store, and their sale prices. I've created pivot table to cut the data, and slice by store, product category, etc. with the value being to show weighted ave price. For ex:

[TABLE="width: 170"]
<tbody>[TR]
[TD="width: 169, bgcolor: transparent, colspan: 2"]Average Selling Prices[/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Clothing[/TD]
[TD="bgcolor: transparent"]

$14.3 ​

[/TD]
[TD="bgcolor: transparent"]

$15.1 ​

[/TD]
[TD="bgcolor: transparent"]

$16.9 ​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Electronics[/TD]
[TD="bgcolor: transparent"]

223 ​

[/TD]
[TD="bgcolor: transparent"]

250 ​

[/TD]
[TD="bgcolor: transparent"]

269 ​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]White Goods[/TD]
[TD="bgcolor: transparent"]

522 ​

[/TD]
[TD="bgcolor: transparent"]

543 ​

[/TD]
[TD="bgcolor: transparent"]

601 ​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sporting Goods[/TD]
[TD="bgcolor: transparent"]

25 ​

[/TD]
[TD="bgcolor: transparent"]

27 ​

[/TD]
[TD="bgcolor: transparent"]

29 ​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Toys[/TD]
[TD="bgcolor: transparent"]

15 ​

[/TD]
[TD="bgcolor: transparent"]

17 ​

[/TD]
[TD="bgcolor: transparent"]

12 ​

[/TD]
[/TR]
[TR]
[TD]Grand Total *[/TD]
[TD]

305.0 ​

[/TD]
[TD]

333.0 ​

[/TD]
[TD]

372.0 ​

[/TD]
[/TR]
</tbody>[/TABLE]

I’m now trying to filter the data such that the table calculates the average selling prices (ASPs) using only the first 100 items of each product (SKU) sold in every year. And I need this to be a fully dynamic measure, because I’ll want to analyze the data using different labels and slicers.


So for instance, I’ll want the table to calculate the average ASP for Clothing items, using only the selling prices of the first100 sweaters, t-shirts, and shorts sold in each year.I will later need to use this measure to display the initial ASP (ie, first 100 items sold) for the top 25 best-selling items of the year (ranked by total sales).I know how to use the TopN function, but not how to filter by time.


Could someone please lend a hand? Thank you!

 

[GDRIII]

Do you have a Calendar table?

 

[psteps]

No - I have all the data in one table, which includes a "sales date" column.

 

[GDRIII]

So here is where PowerPivot will shine as it sings.

Get yourself a calendar table. Here is a link with instructions to get one The Ultimate Date Table « PowerPivotPro

You can also create one pretty easily in Excel.

Caution: No need to bring every date since 1900 until 2099. Keep the calendar relevant to the data.

Once you have one in PP, link up your Invoice date you can really do some amazing things.

If your Sales Date column has an HH:mm time stamp, you might need to add a calculated column to make those reflect just the date.

I would highly recommend reading DAX Formulas for PowerPivot for a full breakdown of Time intelligence formulas and custom calendars as well

Once you are off to the races here, the sky is the limit.

 

[psteps]

Helpful, thank you. But once I have a calendar table, what measure should i use to filter for the first 100 items per year?

 

[GDRIII]

The TopN Filter should work just fine.

I understood your original question to be how to use that measure and filter by dates.

Our top 100 varies year to year so I also use RANKX to actually show the rank in a corresponding column for whatever year I am looking at

 

[psteps]

I am looking for a measure that would filter the selling prices for the first 100 units sold in any given year, and exclude all other units sold after for that given year. I have my calendar table now, but I don't know how to construct the measure. Thanks again for the help..

 

[GDRIII]

Once you have created the relationship between your sales date and your day column in your calendar, you can use all of the time intelligence measures.

I'm not quite sure if you want the first 100 transactions or the Top 100 Items.

Regardless you will want start with something along the lines of =CALCULATE([Total Sales],TOPN(100,tblProducts,[TotalSales])

Then drop Year, Qtr or whatever on a slicer and you should be headed in the right direction.