question

brianpsmith81 avatar image
brianpsmith81 asked

PowerPivot Grouping Issue

Hello all! I am learning PowerPivot/View and am struggling to create an automated chart that meets the request of my company. They want to see the total employee count for each month for the current quarter, and the last month's sum for previous quarters (i.e. Q1 2013 = 200, Q2 2013 = 210, July 2013 = 212, August 2013 = 208). This data is pulled into a SQL data mart once a month at the end of each month and an IsEmployee column contains a 1 for each row that contains an employee. In PowerPivot, I have a calculated column using the following: =IF((MONTH(NOW()))-RELATED(Dates[Month_Of_Year])>2,IF((RELATED(Dates[Month_Of_Year])/3) <> RELATED(Dates[Quarter_Of_Year]), 0, [IsEmployee]), [IsEmployee] ) Which kind of works, but still does not display properly (can't have months in previous quarters rolled up without rolling up months in the current quarter...and showing by months results in blanks 2 out of every 3 months). I think they would also be content with the ability to see quarter aggregates (last month sum only) and then drill down to months (sum), but that is also different aggregates. I'm open to any thoughts or suggestions anyone may have for accomplishing this. Please let me know if I can provide any more information.
calculated-column
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.