Forum Discussion
Column Chart - measure based on axis value
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.
Hi Anonymous ,
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.
1 Reply
- v-eachen-msftCommunity Support
Hi Anonymous ,
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.