Log In
Register
Home
Active Member
June 7, 2018
Offline
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
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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/.....e.1209081/
It would be very helpful if you could share a file with some sample data, the measure and the pivot table.
Active Member
June 7, 2018
Offline
@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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Why not use 1 and 0 to mark an account as Active and Inactive. Then you can filter Active accounts by selecting 1.
VIP
Trusted Members
December 7, 2016
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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])))
The following users say thank you to Velouria for this useful post:
Anders Sehlstedt
1 Guest(s)
