Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |