Hi Mynda,
I am trying to calculate employee count for the most current month. For that, i am using this query:
EMPCOUNT:=CALCULATE(COUNTROWS('Consolidated Report'),FILTER('Consolidated Report','Consolidated Report'[End of Month]=[LASTDATE]))
I am using Last Date function to identify the last date in the dates column & then use it to filter the table to identify employee count for a particular month.
I am not getting the correct answer - infact getting the entire count of rows when it should just throw the count of rows of the last month.
I am attaching the snapshot of my power pivot window.
Thanks
Shantanu
Hi,
Don't expect to see the correct values in Power Pivot editor.
The measures should be designed to calculate the correct values in a pivot table context, the only way to see if your measure works is to create a pivot table and display the measure with your desired pivot table structure.
In power Pivot editor window, [LastDate] will always be ... the last date, because there is no row context in there, there can be a row context only in a pivot table or in a table column.
You might need to use FILTER(ALL('Consolidated Report'),...., in a pivot table context the measure is sensitive to the row context.
Hi,
Thanks for the response but I am not able to crack it. What is wrong in the formula?
Can you attach a screenshot of your pivot table with the wrong count displayed?
Hi,
I am attaching 2 snapshots.
Pivot table that shows the correct number of employees for all months together ( even when the filter is last month filter in the formula above) & the power pivot window.
The last Month measure created-is correcting identifying the last month of that column. However, when I use it in calculate function, it is not filtering the data by last month.
Ideally in the pivot table- when I use the measure EMPCOUNT, I should get the count of only the most recent month & not for all months together.
What am I missing?
Hi Shantanu,
As Catalin said: "In power Pivot editor window, [LastDate] will always be ... the last date, because there is no row context in there, there can be a row context only in a pivot table or in a table column."
Your PivotTable doesn't have a date field in it, therefore the measure has no context to know what is the 'last month'.
Try moving the gender to the column labels and put a date field in the PivotTable row labels.
Mynda
