Column Chart - measure based on axis value

[Anonymous]

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.

[v-eachen-msft · Accepted solution]

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.