Need DAX advises to create measurement based on date of another table

[we3940]

Hi,

I have a table of user details where we have a date column of user start date and a date column of user end date. I can easily creaet a bar chart to see new users every month (create date as X axis) and a bar chart to see disabed users every month (end date as X axis). I want to combine created users and end users in the same line chart. so I created a new seperate calendar table so that I can use that calendar(date) as common x-axis. I tried to create MEASUREMENT for created_users and disabled_users, but I couldn't figure out. In the end, I had to create two columns in the new calendar table as below

• CREATED_USERS = CALCULATE(DISTINCTCOUNT(USER_DETAIL[USER_ID]),FILTER(ALL(USER_DETAIL),USER_DETAIL[CREATION_DATE]=EARLIER('CALENDAR'[Date])))
• DISABLED_USERS = CALCULATE(DISTINCTCOUNT(USER_DETAIL[USER_ID]),FILTER(ALL(USER_DETAIL),USER_DETAIL[END_DATE]=EARLIER('CALENDAR'[Date])))

With the two new columns, I am able to be generate the line charts correctly to compare disabled users vs. created users in the same chart. But I am not very happy with the solutions. It must be possible to create measurement instead of columns.

[we3940]

Hi, Thanks. new DAX works fine. I just tested it and it works as below. 

MEASUREMENT_CREATED_USERS = CALCULATE( DISTINCTCOUNT(USER_DETAIL[USER_ID]), USER_DETAIL[CREATION_DATE] >= MIN('CALENDAR'[Date]) &&

USER_DETAIL[CREATION_DATE] <= MAX('CALENDAR'[Date]))

MEASUREMENT_DISABLED_USERS = CALCULATE( DISTINCTCOUNT(USER_DETAIL[USER_ID]), (USER_DETAIL[END_DATE] >= MIN('CALENDAR'[Date]) && USER_DETAIL[END_DATE] <= MAX('CALENDAR'[Date]))&&USER_DETAIL[END_DATE] <>BLANK() )

[we3940]

Hi, thanks for the explantion. I used your DAX for the two measurements and unfortunately it doesn't work as expected. also I have filtered year 2023 and year 2024 for the calendar(date), but lines chart now shows some data for the earlier years. This looks very strange. 

[HarshSathwara19 · Accepted solution]

Try this If it works

CREATED_USERS =
CALCULATE(
DISTINCTCOUNT(USER_DETAIL[USER_ID]),
USER_DETAIL[CREATION_DATE] >= MIN('CALENDAR'[Date]) &&
USER_DETAIL[CREATION_DATE] <= MAX('CALENDAR'[Date])
)

[HarshSathwara19]

You can directly create a measure using your logic with some improvements. Remove the additional filter, as the measure automatically takes the table through the filter context. The measure might look like the one below (untested)
1. Measure for Created Users:

CREATED_USERS =
CALCULATE(
DISTINCTCOUNT(USER_DETAIL[USER_ID]),
USER_DETAIL[CREATION_DATE] = MAX('CALENDAR'[Date])
)

2. Measure for Disabled Users:

DISABLED_USERS =
CALCULATE(
DISTINCTCOUNT(USER_DETAIL[USER_ID]),
USER_DETAIL[END_DATE] = MAX('CALENDAR'[Date])
)

Best Regards,
HSathwara.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.