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.
We have Dynamics CE in our org and for measuring some metrics around it, we need to know the total number of licensed users for each month.
I have a systemsuser table which has these fields -
Licensed Date - the day the user was assigned a license
License Revoke Date - the day when the user's license was disabled.
I am now trying to plot a column chart that shows the number of licensed users for each month.
I added a measure to calculate the number of licensed users for March -
( Number of users who were licensed before end of the month - Number of users whose license was revoked before the start of the month - Number of users whose license was revoked after the end of the month)
CALCULATE(
COUNT(
systemuser[fullname]),
FILTER(
systemuser,
NOT(ISBLANK(systemuser[new_azurelicenseddate])) &&
systemuser[new_azurelicenseddate].[Date]<DATE(2020,3,31)
)
) - CALCULATE(
COUNT(
systemuser[fullname]),
FILTER(
systemuser,
NOT(ISBLANK(systemuser[new_azurerevokeddate])) &&
systemuser[new_azurerevokeddate].[Date]>DATE(2020,3,31)
)
) - CALCULATE(
COUNT(
systemuser[fullname]),
FILTER(
systemuser,
NOT(ISBLANK(systemuser[new_azurerevokeddate])) &&
systemuser[new_azurerevokeddate].[Date]<DATE(2020,3,1)
)
)
Now, how do I create a measure (or some other way) that will give me the number of licensed users for each month.
Solved! Go to Solution.
Hi @ThatAPIGuy ,
You need a distinct date table to do it and use SELECTEDVALUE() to replace your hardcode.
Here is my sample result.
Here is my test file for your reference.
Hi @ThatAPIGuy ,
You need a distinct date table to do it and use SELECTEDVALUE() to replace your hardcode.
Here is my sample result.
Here is my test file for your reference.
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |