I have an account table (dim table): account name, start date, end date.
The user chooses a year using a disconnected 'year' slicer.
A measure [account status] calculates whether the account is 'active' or 'inactive' depending on the choice of 'year'.
This all works properly.
How do I create a report table showing only the 'active' accounts?
I have tried a powerpivot table with account name, start date and end date in the row field, and the [account status] in the 'values' field, but I cannot filter 'values' to only show 'active' (using the 'values filter' in the rows filed does not work with text, and I would prefer to use a slicer rather than the user having to faff around with filters on a pivot table).
Should I be using a powerpivot table or a different method?
Thanks
You omitted to add a link to your post on MrExel. Found it and added it below so that people answering on this forum can check if an answer has come in before spending time on it.
https://www.mrexcel.com/board/threads/how-to-filter-a-measure.1209081/
It would be very helpful if you could share a file with some sample data, the measure and the pivot table.
@Riny Thanks for adding the link. I meant to do that but forgot. I have attached an example file so hopefully it helps with the question.
Why not use 1 and 0 to mark an account as Active and Inactive. Then you can filter Active accounts by selecting 1.
Hello Fred,
Not sure if this is a good approach in the long run, but at least it works with this small amount of data, even though you have to update the data for every change. Take a look at the file and see if this little cheat trick does the job for you.
Br,
Anders
You could use a measure just for active sales - eg
=CALCULATE([Total sales],FILTER(dimAccounts,[Year selected]>=max(dimAccounts[Year - start date])
&&[Year selected]<=max(dimAccounts[Year - end date])))
