Forum Discussion
Relate 2 date fields from 2 different tables
- Anonymous3 years ago
That got me on the right track but a friend and I got together over lunch and we got really close using variables. One of them was a MAXX flag that would always bring back the second date in the list so we could run logic off of it. It more or less is giving us the correct answer now:
VAR StartDate = SELECTEDVALUE ( Subscription[SubscriptionStartDate] ) VAR LicenseCreatedDate = MIN ( 'Trend'[LicenseCreatedDate] ) VAR SubId = SELECTEDVALUE ( Subscription[SubscriptionId] ) VAR _firstdupe = MAXX ( FILTER ( ALL ( Subscription ), Subscription[SubscriptionId] = SubId ), Subscription[SubscriptionStartDate] ) RETURN IF ( LicenseCreatedDate > StartDate && LicenseCreatedDate < _firstdupe, CALCULATE ( COUNTX ( 'Trend', 'Trend'[PersonId] ), 'Trend'[LicenseCreatedDate] > StartDate && 'Trend'[LicenseCreatedDate] < _firstdupe ), CALCULATE ( COUNTX ( 'Trend', 'Trend'[LicenseCreatedDate] ) ) )
Now all I need to do is remember how to turn the 1 in the below image to 8, since that 1 represents them adding another license on 9/19 but it should be summed to the 7 that came before it.
Anonymous , Try to have a common date table and have measures like in the below blogs
Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
You might need other common dimensions too
That got me on the right track but a friend and I got together over lunch and we got really close using variables. One of them was a MAXX flag that would always bring back the second date in the list so we could run logic off of it. It more or less is giving us the correct answer now:
VAR StartDate =
SELECTEDVALUE ( Subscription[SubscriptionStartDate] )
VAR LicenseCreatedDate =
MIN ( 'Trend'[LicenseCreatedDate] )
VAR SubId =
SELECTEDVALUE ( Subscription[SubscriptionId] )
VAR _firstdupe =
MAXX (
FILTER ( ALL ( Subscription ), Subscription[SubscriptionId] = SubId ),
Subscription[SubscriptionStartDate]
)
RETURN
IF (
LicenseCreatedDate > StartDate
&& LicenseCreatedDate < _firstdupe,
CALCULATE (
COUNTX ( 'Trend', 'Trend'[PersonId] ),
'Trend'[LicenseCreatedDate] > StartDate
&& 'Trend'[LicenseCreatedDate] < _firstdupe
),
CALCULATE (
COUNTX ( 'Trend', 'Trend'[LicenseCreatedDate] )
)
)
Now all I need to do is remember how to turn the 1 in the below image to 8, since that 1 represents them adding another license on 9/19 but it should be summed to the 7 that came before it.
- Anonymous3 years agoNot applicable
We ended up going a different route but I will mark my last one as a solution because it was close enough.