Issue with Missing data in Calculation

Question

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]

)

mnedix · Answer

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?

kedar_pande · Answer

ginalu&nbsp;You can try the updated DAX:Recent7DaysActiveUsers =CALCULATE (DISTINCTCOUNT ( USERUNQ[user_id] ),FILTER (LESSONN,TRUNC ( LESSONN[start_time] )&gt;= TODAY () - 7&amp;&amp; TRUNC ( LESSONN[start_time] ) &lt; TODAY ()),USERUNQ[user_id] = LESSONN[teacher_id])Ensure that start_time in LESSONN is in a date format&nbsp;💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉Cheers,KedarConnect on LinkedIn&nbsp;

ginalu · Answer

Hi&nbsp;Kedar_Pande&nbsp;,&nbsp;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.&nbsp;

sanalytics · Answer

Hello&nbsp;ginalu&nbsp;You can use below DAX&nbsp;Recent7DaysActiveUsers =
CALCULATE (
DISTINCTCOUNT ( USERUNQ[user_id] ),
FILTER (
Summarize(
LESSONN,LESSONN[start_time],LESSONN[teacher_id]
),
TRUNC ( LESSONN[start_time] )
&gt;= TODAY () - 7
&amp;&amp; TRUNC ( LESSONN[start_time] ) &lt; TODAY ()
),
USERUNQ[user_id] = LESSONN[teacher_id]
)&nbsp;instead of filtering whole table, you can summarize the table with required columns.&nbsp;Regardssanalytics