DAX QUERY FOR 12MONTH ROLLING DISTINCT COUNT

[Anonymous]

Hi,

I am trying to report the number of users that are active per month on my report and also the number of users that have been registered the last 12 months -year to date on a monthly basis.(users automatically come off the list if they havent been active for 12 months)

For example I want to say on January we had 110 active users and 1500 enrolled users in total and all this to go on a rolling 12 month basis so we can see the flactuation on the enrolled users every month.

My first part of muber of users on a monthly basis was simply shorted with a distinct count formula,however I am struggling with the second part of it I tried with a summirise formula and distinct count but it didnt seem to work.Both comulns will pick up data from the same sheet(reported users) and the same tables which are Date table and User ID name table.

Can you help please?

Thank you in advance

[v-shex-msft]

HI @Anonymous,

You can also take a look at the following blog to use date functions to manually define filter range to calculate, it is more simply to usage and has less limitation than time intelligence functions:

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , You can use a rolling measure with date calendar

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.