Hello! I'm new to PowerPivot and don't know all the terminology yet, sorry if this has been answered before.
I created a sample table where I have the Movie Title, Movie Studio, Type, Reporting Period, Quantity Sold, and Date of Report. There are two Types, DVD and BlueRay. The sales for these Types come in on two different reports. There are also multiple amended sales (either due to additional sales or returns). As a result, I need to filter for the MAX Date of Report for both DVD and BlueRay, SUM the sales of DVDs and BlueRays based on the respective MAX Date of Report and they have a slicer or filter for the Reporting Period (month).
The problem I'm running into is the MAX Date of Report needs to be based on Reporting Period (selected on slicer) and Type, so only the MAX Date of Report for all BlueRays is returned for March, not the MAX Date for March for BlueRays for each Movie Studio.
For the measures I have
Max Date of Report - BlueRay =CALCULATE(MAX([Date of Report]),Sales[Type]="BlueRay")
Max Date of Report - DVD =CALCULATE(MAX([Date of Report]),Sales[Type]="DVD")
=CALCULATE(SUM(Sales[Quantity Sold]),Sales[Type]="BlueRay",FILTER(Sales,[Date of Report]=[Max Date of Report - BlueRay]))
=CALCULATE(SUM(Sales[Quantity Sold]),Sales[Type]="DVD",FILTER(Sales,[Date of Report]=[Max Date of Report - DVD]))
I have attached my table and screenshots.
Thanks in advanced!
Table.JPG
PowerPivot.JPG
Bookmarks