Issue with Missing data in Calculation

[ginalu]

Some lessons are included while others are missing in the calculation. 

Example of Missing Lesson in Calculation

For example, a user conducted a lesson on 10/16. Since today is 10/21, the lesson should be counted as an activity within the last seven days. However, it is not being included in the calculation as expected.

The formula and data are as shown in the image below:

Recent7DaysActiveUsers =

CALCULATE(

    DISTINCTCOUNT(USERUNQ[user_id]),

    FILTER(

        LESSONN,

        TRUNC(LESSONN[start_time]) >= TODAY() - 7 &&

        TRUNC(LESSONN[start_time]) < TODAY() &&

        USERUNQ[user_id] = LESSONN[teacher_id]

    )

)

[Anonymous]

Hi,@ginalu .I am glad to help you.

Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Anonymous]

Hi,MNedix, sanalytics and Kedar_Pande .

thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@ginalu .I am glad to help you.

Here is my test.

I noticed that you have a one-to-many relationship between your two tables, try using the RELATED function in the filter function on a field in the other table

like this.

Calculate the number of users who have been active in the last seven days.

Recent7DaysActiveUsers = 
CALCULATE(
    DISTINCTCOUNT(USERUNQ[user_id]),
    FILTER(
        LESSONN,
        TRUNC(LESSONN[start_time]) >= TODAY() - 7 &&
        TRUNC(LESSONN[start_time]) < TODAY() &&
        RELATED(USERUNQ[user_id]) = LESSONN[teacher_id]
    )
)

Below is my test data:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanalytics]

Hello @ginalu 
You can use below DAX

Recent7DaysActiveUsers =
CALCULATE (
DISTINCTCOUNT ( USERUNQ[user_id] ),
FILTER (
Summarize(
LESSONN,LESSONN[start_time],LESSONN[teacher_id]
),
TRUNC ( LESSONN[start_time] )
>= TODAY () - 7
&& TRUNC ( LESSONN[start_time] ) < TODAY ()
),
USERUNQ[user_id] = LESSONN[teacher_id]
)

instead of filtering whole table, you can summarize the table with required columns.

Regards

sanalytics

[ginalu]

Hi @sanalytics 

Thank you for your reply. I tried it, but an error message appeared:

The expression includes columns from multiple tables, but only columns from a single table can be used in a True/False expression for table filtering.

[Kedar_Pande]

@ginalu 

You can try the updated DAX:

Recent7DaysActiveUsers =
CALCULATE (
DISTINCTCOUNT ( USERUNQ[user_id] ),
FILTER (
LESSONN,
TRUNC ( LESSONN[start_time] )
>= TODAY () - 7
&& TRUNC ( LESSONN[start_time] ) < TODAY ()
),
USERUNQ[user_id] = LESSONN[teacher_id]
)

Ensure that start_time in LESSONN is in a date format

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[ginalu]

Hi @Kedar_Pande ,

Thank you for your reply. I tried it, but an error message appeared:

The expression includes columns from multiple tables, but only columns from a single table can be used in a True/False expression for table filtering.

[MNedix]

Heya,

I think TRUNC throws a spanner in the works. Could you please try DATEVALUE instead? Also, I don't know your data but are you sure about the last filter argument?

[ginalu]

Hi @MNedix 

Thank you for your reply. I tried it, but it still missing in the calculation.

[MNedix]

Hi,

Could you please split in Power Query the start_time column by delimiter? Try to isolate only the date portion of that column, I remember it helped me in the past. Then adjust the calculation accordingly.