Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am very new to power Bi and DAX and need help. I have records of dates for when a account was created(Activated Date) for a user. The records go back to 2012. I am showing data for each month of 2022. And I want to show how many people were still active before the last date of each month. I dont want to count people who have a In-Activated date or who have Activated date after the respective month. right now the formula looks like
Solved! Go to Solution.
Hi @Anonymous ,
Here I create a sample to have a test.
DimDate table:
DimDate =
ADDCOLUMNS( CALENDAR(DATE(2022,01,01),DATE(2022,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthLongName",FORMAT([Date],"MMMM"))
Please try this code to create a measure to count the active user before each month ending date.
Measure:
Active User =
VAR _MAXDATE =
MAX ( DimDate[Date] )
RETURN
CALCULATE (
COUNT ( RegisteredUsers[User Name] ),
FILTER (
RegisteredUsers,
RegisteredUsers[Activated Date] <= _MAXDATE
&& OR (
RegisteredUsers[In-Activated Date] = BLANK (),
RegisteredUsers[In-Activated Date] > _MAXDATE
)
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Here I create a sample to have a test.
DimDate table:
DimDate =
ADDCOLUMNS( CALENDAR(DATE(2022,01,01),DATE(2022,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthLongName",FORMAT([Date],"MMMM"))
Please try this code to create a measure to count the active user before each month ending date.
Measure:
Active User =
VAR _MAXDATE =
MAX ( DimDate[Date] )
RETURN
CALCULATE (
COUNT ( RegisteredUsers[User Name] ),
FILTER (
RegisteredUsers,
RegisteredUsers[Activated Date] <= _MAXDATE
&& OR (
RegisteredUsers[In-Activated Date] = BLANK (),
RegisteredUsers[In-Activated Date] > _MAXDATE
)
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , I think You need something similar to this HR blog
Or check the attached files
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |