Hi Mamie,

Welcome to our forum! We'd need to see your file or a mockup of your file, or at a minimum, screenshots so we can understand the filter context you're wanting to apply.

Mynda

Hi Mynda,

Here is what my Pivot table is looking like with no filters applied:

When I filter the data, the numbers for the month are there, but the subtotal for the year is gone. I got a little closer when I redid the measure to be the following (the totals are still present) but it's totaling everything instead of just giving me the most recent total based on my filter choices (location, business unit, etc.).

=CALCULATE(SUM(Consolidated_Dashboard_Table_10[Actual Headcount]),FILTER(ALL(Consolidated_Dashboard_Table_10[Formatted Date]),LASTDATE(Consolidated_Dashboard_Table_10[Formatted Date])))

Hi Mynda,

Here is my note with more details. 

https://privnote.com/RI5wdC6Y#AlHJTuUxQ

I think the issue is in the FILTER part of my formula, I just can't figure out which part. 

I appreciate your help!

Mamie

Hi Mamie,

I couldn't open the privnote and the screenshots don't tell the story enough for me to help. Please create a dummy/mockup file containing some unsensitive data. It doesn't need to be a lot of data. Just enough to illustrate the issue and represent all possible scenarios.

Thanks,

Mynda

Hi Mynda,

Aw that's too bad! Maybe it was user error on my part...

I wasn't sure what the best way to scale down the file without throwing lots of things off so I relabeled everything to be generic. I just tried to load the dummy file I made and it's too big to post here. 🙁

Here is the link to the Dropbox folder if that's helpful: https://www.dropbox.com/sh/sma20hc37zb6bcn/AACbZL8UXqGhotWsYcEyiP0Ha?dl=0

Thanks!

Mamie

Hi Mamie,

Thanks for sharing your file. You can use the implicit measures already built into PivotTables for these calculations. That is, build a PivotTable with the following fields:

Rows:

Formatted Date (grouped by Year and Month)

Employee Type

Values:

Actual Headcount

Budget Approved Headcount

Columns:

Values

Right-click on the month name in the PivotTable > select Subtotal by Month. Repeat for the Years field.

Hope that points you in the right direction.

Mynda

Hi Mynda,

Thanks for your reply! I hadn't even thought about using the Formatted Date because I just recently added that.

I get really close with what you outline above, but I'm still stuck on getting the Year total to show me the most recent Month total where the headcount is greater than 0. There are some locations that will not have data for the most recent date in the workbook but I still need to show their most recent headcount. How can I do this? 

I appreciate your help! 🙂

In the Power Pivot course, it was suggested to try to use Explicit Measures rather than Implicit Measures.

Does Mamie's question lend itself more to an Implicit Measure?  If that's the case I'm not quite sure when best to use Explicit vs Implicit Measure.  Is this just based on experience with Power Pivot?

Thanks,

Steve 

Hi Mynda,

I went back to your HR Dashboard video and I seem to have gotten this to work by writing another measure based on what you wrote for separations in the HR Dashboard video as follows:

=CALCULATE([Actual Headcount Calc],FILTER(Consolidated_Dashboard_Table_10,NOT(ISBLANK([Actual Headcount Calc]))))

This is now giving me the most recent month total as a subtotal for the year even if the max date in the table has no data. For example, the most recent date in my file is July 2021 but my biggest location problem child didn't have any data for July - their most recent data was June. Now the June total is showing in the 2021 subtotal instead of a blank (or 0), which is exactly what I wanted. 🙂 🙂 I'm so happy right now!

@Mamie, I see. I misunderstood your question. Glad you got it working.

@Steve, explicit measures are ideal when you need to write further DAX measures based on what would normally be an implicit measure e.g. something basic like a SUM or COUNT etc. But when you only need something basic like a SUM or COUNT, which it what I thought Mamie needed, then there's no need to over engineer it with explicit measures. Hope that makes sense.