topic YTD of results of monthly unique user count in DAX Commands and Tips

YTD of results of monthly unique user count

mgr13 — Sun, 23 Jul 2023 18:31:48 GMT

Hi
I have a situation
Table name : app_interaction
Columns: appname, activity, userid, created_on(date)

Now I want to bulid the table visual with year, month, distinct count(userid) as count of unique IDs , and next I want I want calculate the total YTD of unique user IDs,

Here while calculating
If Jan has 12 unique users and Feb has 10 unique

So YTD I need is 32 because I have the monthly cost/user to be multiplied to the value 32

The problem is it's giving me the less than the 32. As it's removing the number of common users in two months

Can someone please help me to do this

Re: YTD of results of monthly unique user count

Manoj_Nair — Sun, 23 Jul 2023 23:57:00 GMT

mgr13- Check this out, let me know if this works.

YTD Unique User IDs = CALCULATE( DISTINCTCOUNT('app_interaction'[userid]), CALCULATETABLE( VALUES('app_interaction'[userid]), DATESYTD('app_interaction'[created_on]) ) )

I've hypothetically established this DAX measure, but without much clarity regarding your datasets. If this doesn't serve your needs, I recommend you share a sample dataset. This will give me a more comprehensive understanding of your data structure, enabling me to devise a more suitable solution. Cheers

Re: YTD of results of monthly unique user count

mgr13 — Mon, 24 Jul 2023 07:59:45 GMT

Thanks for sharing ur insignt Manoj_Nair

I have tried the dax code that you have given but it's giving the same as unique user IDs

but what I need is the Excel image I shown

here I am providing the pic of a data sample

could you refer thes information and provide me some guidance if possible

Thanks

Re: YTD of results of monthly unique user count

Anonymous — Tue, 25 Jul 2023 07:28:51 GMT

Hi mgr13

You can refer to the following measure

Sample data

Measure

Measure = var a=SUMMARIZE(ALLSELECTED(app_interaction),app_interaction[created_on].[Year],app_interaction[created_on].[MonthNo],"Distinct",CALCULATE(DISTINCTCOUNT(app_interaction[userid]))) return IF(DISTINCTCOUNT(app_interaction[userid])<>BLANK(),SUMX(FILTER(a,[created_on].[Year] in VALUES(app_interaction[created_on].[Year])&&[created_on].[MonthNo]<=SELECTEDVALUE(app_interaction[created_on].[MonthNo])),[Distinct]))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: YTD of results of monthly unique user count

mgr13 — Mon, 31 Jul 2023 19:35:04 GMT

Hi Anonymous

Thanks for your support.

I have a question

For the above data

I had to calculate the past 90 day unique users for every month and the next colum I need to do the YTD of the above in a table visual.

Thanks

Guru

Re: YTD of results of monthly unique user count

mgr13 — Thu, 03 Aug 2023 14:34:00 GMT

Any one please help me out for this