Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

1 Reply