PBI Active Users calculation

[schdef]

Trying to calculate Monthly Active users with the follwoing measure:

MAU = CALCULATE(
    SUMX(VALUES('Usage '[Alias]),1),
    ALL('Usage'[FullDate]),
    DATESINPERIOD('Usage'[Date], LASTDATE('Usage'[Date]), -30, DAY)
)

This basically works fine and as expected. Using the measure in a trend chart, there is just one issue with the dates where we do not have distinct users - this leads to the situation that the DATESINPERIOD is not calculated correctly as there appears to be not start date. For this date only the users 30 days ago are being considered which is not what i  want. See the screenshot attached.

Any thoughts how to fix that? Many thanks!

[v-sihou-msft · Accepted solution]

@schdef

In this scenario, I don't understand why you have both [FullDate] and [Date] column in your table. I think your Usage table is a fact table which should include a Date column only. Then you can have another Full Date table and build relationship to Usage table. When can calculate the active users, you should use Full Date to limit the date range in DATESINPERIOD() function.

Regards,