Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi friends,
I'ver really tried hard to search for a solution to my query but have not found anything yet, leading me to doubt if it can be done at all. So here's what I'm trying to do -
Premise - In a certain software, users(auto technicians) have created Repair Plans (think of them as work orders) over a period of time. Every Repair Plan created has a unique ID . I also have the dates(CreateDate) of the users' userid creation.
UserName - unique email ids of auto technicians across various auto centers.
UserID - unique IDs that identify those auto technicians
CreateDate(think of it as a start date - Start date for a UserID (basically, the day a technician had his userID created in the software)
Count of PlanId- count of Plans created by each UserID
I already have the count of the ALL PlanIds ever created by a UserId - 'Count of PlanId'. What I'm required to do is add another column to the right of 'Count of PlanId' - 'Count of PlanId 60 days after CreateDate'
Basically I need help adding a column that shows Count of Repair Plans for each UserId, 60 post their respective 'CreateDate'.
Kindly help me!
I appreciate any hints/tips that would help me calculate this column.
Thankyou dear members!
Solved! Go to Solution.
Hi @Sylvester_OEC
have you tried
Count of PlanId 60 days after CreateDate =
Calculate(
Count('Table'[PlanId]),
Dateadd('Table'[CreateDate],60,DAY)
)
Regards
Hi @Sylvester_OEC ,
According to your statement, I think there should be a [Plan created date] for each Plan ID in your data model. Now you want to count the Plan ID which are 60 days after the user created date. You can add a filter in your count measure.
My sample:
Measure:
Count of PlanId 60 days after CreateDate =
VAR _60DAYS_AFTER =
MAX ( 'Table'[CreateDate] ) + 60
RETURN
CALCULATE (
COUNT ( 'Table'[PlanId] ),
FILTER ( 'Table', 'Table'[Plan Date] > _60DAYS_AFTER )
)
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 @Sylvester_OEC ,
According to your statement, I think there should be a [Plan created date] for each Plan ID in your data model. Now you want to count the Plan ID which are 60 days after the user created date. You can add a filter in your count measure.
My sample:
Measure:
Count of PlanId 60 days after CreateDate =
VAR _60DAYS_AFTER =
MAX ( 'Table'[CreateDate] ) + 60
RETURN
CALCULATE (
COUNT ( 'Table'[PlanId] ),
FILTER ( 'Table', 'Table'[Plan Date] > _60DAYS_AFTER )
)
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 @Sylvester_OEC
have you tried
Count of PlanId 60 days after CreateDate =
Calculate(
Count('Table'[PlanId]),
Dateadd('Table'[CreateDate],60,DAY)
)
Regards
Hi Aditya,
I really apprciate you trying to helo me out. Also, I did try the dax you shared, but it retruned the following an error -
Could you advise me how to proceed.
Thanks so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |