Hi - i I have an issue where i've got multiple Date columns (from a Dynamics 365 CRM database).
'Membership Start Date'
'Membership Expiry Date'
I've also got a 'Membership Status' Column (Values either 'Active' or 'Expired'), but this just tells you whether that member is Active right now, not on a specific date in the past.
All 3 of these columns are in one table.
What I need to do is determine our Active membership numbers on a specific date, so I would like to create a Slicer whereby people using this report can just select 'Report Date' (no field/table currently exists for this) to see our Active membership numbers at that specific date.
To determine if they were Active on that Report date, we would need the following:
1. 'Membership Start Date' to be Before 'Report Date', 'Membership Expiry Date' to be Blank OR After 'Report Date', 'Membership Status'=Active
2. 'Membership Start Date' to be Before 'Report Date', 'Membership Expiry Date' to be After 'Report Date', 'Membership Status'=Expired
New to Power BI/DAX so not sure on how to achieve this. Also the formatting of my Date Columns is just as they've been imported from Dynamics 365 (i.e. 01/01/1900 00:00:00), so dont know if this formatting will need to be changed before starting the above?
Some guidance would be appreciated. 🙂
