Forum Discussion
using calendar year and date from another table
Hi All,
I am trying to create a visual as per below requirements but need help in connecting two dates.
Data Points -
Policy Issued Date
Calendar Date
Annual Premium
Policy Stage
So now I need to create chart to show Active policies in year of 2017 which can be done using Policy Issue Date when I select filter of Year (Date table related to Policy Issued date). What I am trying to do is - when you select 2018, it should show both 2017 and 2018 policies.. because some of the polcicies will still be active in 2018 however issued in 2017 and so on.. but right now, it is only show the particular year policies. When I select 2019, it should all active policies from 2017 and 2018. I was thinking filter should be calendar year and somehow need to connect/relate the date tables.
Thanks,
Swati
2 Replies
- foodd
Community Champion
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied. - MarkLaf
Super User
I recommend getting inspiration from the 'events in progress' pattern: https://www.daxpatterns.com/events-in-progress/
I think you are saying that your policies don't have end dates, so you can follow the above pattern and just remove the end date portion.
To try to summarize quickly:
1) Create a Date table. See the following if not already familiar with doing this: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
2) To get a count of active policies, something similar to the following should work:
# Active Polices ALL := VAR MinDate = MIN ( 'Date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( 'Data Points' ), 'Data Points'[Policy Issued Date] >= MinDate, REMOVEFILTERS ( 'Date' ) ) RETURN Result
