Count new records by month

[RobThrive]

Hello all,

I have a data model like so:

New Users Reg Table     --> Fact Table <-- Date Table

Uid                                 --> Uid             <other cols>

<other cols>                        Date Login <-- Date Column

Date_Created                       <other cols>

The arrows above show the relationships.

Generally the business have an interest is in the fact table which shows how often people login, when, etc.

But I have a new requirement to work out how many people have had their accounts created for each month, that data is in the New Users Reg table.

So if the user has changed the slider to show login data between october and november, I also need to show how many accounts were created during those two months as well.

Currently I keep getting the same count for every month, or getting completely wrong numbers.

Many thanks for any help.

[CNENFRNL]

Hi, @RobThrive , you might want to try this measure,

New UIDs = 
VAR __reg_date =
    CALCULATETABLE ( users_field_data, CALCULATETABLE ( Login_Tracker ) )
RETURN
    SUMX (
        DISTINCT ( DateTable[CalendarDate] ),
        CALCULATE (
            DISTINCTCOUNT ( users_field_data[Uid] ),
            FILTER ( __reg_date, users_field_data[Created_Date] = DateTable[CalendarDate] )
        )
    )

[amitchandak]

@RobThrive , in this case, date table should be joined with date created and date login of fact. and then you can analyze both data with dates od date table .

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[RobThrive]

Hi @amitchandak 

Wasn't sure how to upload PBIX files to the forum, says they are not supported. Here is a link to download a sample file though

https://www.swisstransfer.com/d/a5805a7c-24b7-4b86-819c-532b0ddf05b8

One trouble is that I have several different groups of data models inside (real report is much larger than this sample one) and the date table is used to connect to 3-4 fact tables. I wasn't sure how connecting to it the dimension table with the date of new accounts created, would work.

Thanks