Forum Discussion
swtgoyal
3 years agoFrequent Visitor
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...
MarkLaf
Super User
3 years agoI 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