Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sylvester_OEC
Frequent Visitor

How to show results, x no of days after a certain date

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
Screenshot (610).png

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!

2 ACCEPTED SOLUTIONS
Aditya_Meshram
Solution Supplier
Solution Supplier

Hi @Sylvester_OEC 
have you tried 

Count of PlanId 60 days after CreateDate =
Calculate(
     Count('Table'[PlanId]),
     Dateadd('Table'[CreateDate],60,DAY)
)


Regards

View solution in original post

v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_1-1652167703575.png

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.

RicoZhou_0-1652167686554.png

 

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.

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_1-1652167703575.png

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.

RicoZhou_0-1652167686554.png

 

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.

Aditya_Meshram
Solution Supplier
Solution Supplier

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 - Screenshot (659).png
Could you advise me how to proceed.

Thanks so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors