Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Trying to solve a measure for a subscription based business.
The measure should calculate number of customers per month.
I work with a dataset that stores all the changes on the subscription. I can therefore have man rows per subscription.
The measure works fine except when a subscription is moved between Business units.
The customer then counts on both buinsess units instead of just the correct one.
The measure also counts correct when looking at the whole business.
I tried adding All(Business units) in both MaxPricedates and Results without effect.
Measure:
Number customers =
VAR MaxDate =
MAX ( Date[Date] )
VAR FromDate =
ENDOFMONTH(Date[Date])
VAR EndDate =
ENDOFMONTH('Subscriptions'[ActualDate])
VAR EndOfLifeDate =
STARTOFMONTH(Date[Date])
VAR MaxPrisDates =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ('Subscriptions','Subscriptions'[SubscriptionID]),
"@MaxBalanceDate", CALCULATE (Max('Subscriptions'[ActualDate]))
),
Date[Date] <= MaxDate
)
VAR MaxPrisDatesWithLineage =
TREATAS ( MaxPrisDates, 'Subscriptions'[SubscriptionID], Date[Date])
VAR Result =
CALCULATE(
DISTINCTCOUNT( 'Subscriptions'[AccountID]),
MaxPrisDatesWithLineage,
'Subscriptions'[Start date] <= FromDate,
'Subscriptions'[End date] >= EndOfLifeDate,
'Subscriptions'[Status] <> "Other",
'Subscriptions'[Status] <> "Stopped"
)
RETURN
Result
Solved! Go to Solution.
Solutions was actually what i already tried, that is to use All(Business Units). This time in the right place.
Number customers =
VAR MaxDate =
MAX ( Date[Date] )
VAR FromDate =
ENDOFMONTH(Date[Date])
VAR EndDate =
ENDOFMONTH('Subscriptions'[ActualDate])
VAR EndOfLifeDate =
STARTOFMONTH(Date[Date])
VAR MaxPrisDates =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ('Subscriptions','Subscriptions'[SubscriptionID]),
"@MaxBalanceDate", CALCULATE (Max('Subscriptions'[ActualDate]))
),
All('Business Units'),
Date[Date] <= MaxDate
)
VAR MaxPrisDatesWithLineage =
TREATAS ( MaxPrisDates, 'Subscriptions'[SubscriptionID], Date[Date])
VAR Result =
CALCULATE(
DISTINCTCOUNT( 'Subscriptions'[AccountID]),
MaxPrisDatesWithLineage,
'Subscriptions'[Start date] <= FromDate,
'Subscriptions'[End date] >= EndOfLifeDate,
'Subscriptions'[Status] <> "Other",
'Subscriptions'[Status] <> "Stopped"
)
RETURN
Result
@Bian ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Solutions was actually what i already tried, that is to use All(Business Units). This time in the right place.
Number customers =
VAR MaxDate =
MAX ( Date[Date] )
VAR FromDate =
ENDOFMONTH(Date[Date])
VAR EndDate =
ENDOFMONTH('Subscriptions'[ActualDate])
VAR EndOfLifeDate =
STARTOFMONTH(Date[Date])
VAR MaxPrisDates =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ('Subscriptions','Subscriptions'[SubscriptionID]),
"@MaxBalanceDate", CALCULATE (Max('Subscriptions'[ActualDate]))
),
All('Business Units'),
Date[Date] <= MaxDate
)
VAR MaxPrisDatesWithLineage =
TREATAS ( MaxPrisDates, 'Subscriptions'[SubscriptionID], Date[Date])
VAR Result =
CALCULATE(
DISTINCTCOUNT( 'Subscriptions'[AccountID]),
MaxPrisDatesWithLineage,
'Subscriptions'[Start date] <= FromDate,
'Subscriptions'[End date] >= EndOfLifeDate,
'Subscriptions'[Status] <> "Other",
'Subscriptions'[Status] <> "Stopped"
)
RETURN
Result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |