Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |