YTD of results of monthly unique user count

[mgr13]

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

[mgr13]

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

[mgr13]

Any one please help me out for this

[Manoj_Nair]

@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

[mgr13]

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

[Anonymous · Accepted solution]

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.