Need help in Time intelligent DAX

[Anonymous]

Hi experts, I'm newbie in this field. I stucked with an DAX formular for few weeks :((

Here is my context:

Calculated the Current Users day by day.

Current User means: How many cumulative CREATED users at that day.

Table: Fact_Login Event: contain LOGIN event for each user

Table: Dim_Total User: List of created users

Please kindly look at my model:

I used this DAX to calculated the current user each day

Current Usser = 
CALCULATE (
    COUNTX (
        FILTER (
            'Dim_Total Users',
            'Dim_Total Users'[Created Date] <= MAX ( 'Date'[Date] )
                && (
                    ( ISBLANK ( 'Dim_Total Users'[deactivate_date] ) = TRUE () )
                        || ( 'Dim_Total Users'[deactivate_date] >= MAX ( 'Date'[Date] ) )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[reactivated_date] <= 'Dim_Total Users'[deactivate_date]
                                && 'Dim_Total Users'[status] <> -1
                        )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[status] <> -1
                        )
                )
                && 'Dim_Total Users'[metatype] <> "guest"
        ),
        'Dim_Total Users'[id]
    ),
    CROSSFILTER ( 'Dim_Total Users'[Created Date], 'Date'[Date], NONE )
)

This is the result:

As you can see, with the above DAX, I will get all the current user for each day, from the 1st day of this specific company (7/3/2020) till the end of date in Date table.

But now, I just want to see only result in 1 specific month, for example, in July 2020.

Step 1: I added a caculated column in Fact_Login Event, to detect event that happend in July - 2020, I will marked "1" to this event.

Step 2: I re-write the DAX like below

Current Usser m+1 = 
CALCULATE(CALCULATE (
    COUNTX (
        FILTER (
            'Dim_Total Users',
            'Dim_Total Users'[Created Date] <= MAX ( 'Date'[Date] )
                && (
                    ( ISBLANK ( 'Dim_Total Users'[deactivate_date] ) = TRUE () )
                        || ( 'Dim_Total Users'[deactivate_date] >= MAX ( 'Date'[Date] ) )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[reactivated_date] <= 'Dim_Total Users'[deactivate_date]
                                && 'Dim_Total Users'[status] <> -1
                        )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[status] <> -1
                        )
                )
                && 'Dim_Total Users'[metatype] <> "guest"
        ),
        'Dim_Total Users'[id]
    ),
   CROSSFILTER ( 'Dim_Total Users'[Created Date], 'Date'[Date], NONE )),FILTER(Fact_Login Event,Fact_Login Event[Column] = 1))

(Just adding "FILTER(Fact_Login Event,Fact_Login Event[Column] = 1)" at the end)

Here is the result:

It's seem like good. But wait... I lost many dates in July: 7/4/2020, 7/5/2020 ..... That's not what I want. The correct result will look like:

- Whole date in July-2020 (start from 7/3/2020)

- Just only date in July-2020 (no data from Aug, Sep...)

The reason why it lost date :7/4/2020, 7/5/2020 ... because of there is no data for those date in table Fact_Login Event.

Please help me fix the 2nd DAX to show whole day in July 2020.

Thanks in advance