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

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

Reply
Anonymous
Not applicable

Subtract between two different date columns based on third column values start date and end date

Hi Team,

 

Need inputs on executing below scenerio. as part of our tracking , we want to create a graph based on the activities.. Please find below table

IDActivity NameStart DateEnd DateDifference in Days
1Review4/5/20178/5/20174
1assignment8/5/201710/5/20172
1approval10/5/201712/5/20172
2Review1/5/20178/5/20177
2assignment8/5/201711/5/20173
2approval11/5/201714/5/20173

 

ID=1Review[Start Date]-Assignment[End Date]=4/5/2017-10/5/2017=6
ID=2Review[Start Date]-Assignment[End Date]=1/5/2017-11/5/2017=10

 

So for all the project IDs, we want to track for example turn around time for a perticular project ID right from review start date to assignment end date. I am confused whether to use DAX or M language to achieve the above scenerio. It will be of great help if some one share the script to progress on this requirement.

 

Regards,

Sivapratap.

 

 

6 REPLIES 6
Eric_Zhang
Microsoft Employee
Microsoft Employee

@Anonymous

Instead of the dates subtraction, can you just add the difference in your case?

 

turn around time assignment_review =
CALCULATE (
    SUM ( 'Table'[Difference in Days] ),
    FILTER (
        'Table',
        'Table'[Activity Name] = "Review"
            || 'Table'[Activity Name] = "Assignment"
    )
)

Otherwise, try

 

turn around time assignment_review 2 = 
INT (
    MAXX (
        FILTER ( 'Table', 'Table'[Activity Name] = "Assignment" ),
        'Table'[End Date]
    )
        - MAXX (
            FILTER ( 'Table', 'Table'[Activity Name] = "Review" ),
            'Table'[Start Date]
        )
)

Capture.PNG

 

 

Anonymous
Not applicable

Thanks for the input. I will try executing and let you know

Anonymous
Not applicable

Hi All,

 

Below is the exact requirement, i mean i will illustrate how my data looks and what should be the outcome:

 

Request NumberActivity NameActivity DateAction DateTime Spent in Days
1Active Item3/29/2017 0:003/29/2017 0:000
1Design Review2/24/2017 0:002/28/2017 0:004
1IT Review2/7/2017 0:002/7/2017 0:000
1Project Initiation3/29/2017 0:003/29/2017 0:000
1PS3/1/2017 0:003/1/2017 0:000
1PS3/8/2017 0:003/8/2017 0:000
1PS3/15/2017 0:003/15/2017 0:000
1Request Initiation2/6/2017 0:002/6/2017 0:000
1RPA3/8/2017 0:003/15/2017 0:007
1RPA3/15/2017 0:003/29/2017 0:0014
1Initial Review2/6/2017 0:002/7/2017 0:001
1SMP Review3/1/2017 0:003/6/2017 0:005
1SMP Review3/8/2017 0:003/8/2017 0:000
1SMP Review3/15/2017 0:003/15/2017 0:000
1Vendor Assignment2/7/2017 0:002/7/2017 0:000
1Vendor Lead Review2/7/2017 0:002/7/2017 0:000
1Vendor Response2/7/2017 0:002/24/2017 0:0017
2Active Item3/22/2017 0:003/22/2017 0:000
2Design Review3/7/2017 0:003/14/2017 0:007
2Project Initiation3/22/2017 0:003/22/2017 0:000
2PS3/16/2017 0:003/16/2017 0:000
2Request Initiation2/27/2017 0:002/27/2017 0:000
2RPA3/17/2017 0:003/20/2017 0:003
2Initial Review2/27/2017 0:002/27/2017 0:000
2SMP Review3/16/2017 0:003/16/2017 0:000
2SMP Review3/16/2017 0:003/17/2017 0:001
2Vendor Assignment2/27/2017 0:002/27/2017 0:000
2Vendor Lead Review2/27/2017 0:003/1/2017 0:002
2Vendor Response3/1/2017 0:003/7/2017 0:006
3Cancellation Request by Requestor3/2/2017 0:003/2/2017 0:000
3Request Initiation2/28/2017 0:002/28/2017 0:000
3Requestor Cancellation Review by SDC Manager3/2/2017 0:004/7/2017 0:0036
3PS3/5/2017 0:003/6/2017 0:001
3Initial Review2/28/2017 0:003/1/2017 0:001
3Vendor Assignment3/1/2017 0:003/1/2017 0:000
3Vendor Lead Review3/1/2017 0:003/1/2017 0:000
3Vendor Response3/1/2017 0:004/7/2017 0:0037
4Request Initiation2/28/2017 0:002/28/2017 0:000
4Initial Review2/28/2017 0:003/3/2017 0:003
4Vendor Assignment3/3/2017 0:003/3/2017 0:000
4Vendor Lead Review3/3/2017 0:003/3/2017 0:000
4Vendor Response3/3/2017 0:004/7/2017 0:0035
5IT Review3/1/2017 0:003/2/2017 0:001
5Request Initiation2/28/2017 0:002/28/2017 0:000
5Initial Review2/28/2017 0:003/1/2017 0:001

 

Requirement:

Difference between PS Action Date and Vendor Assignment Activity Date

 

Please note,

Here we have three records  PS for a perticular request number, so we need to consider the latest i.e  3/15/2017- 2/7/2017 = 36

 

the outcome table should be something like below

ID Action Date MonthTAT(Custom Column)
1March36
2Feb17
3Jan22

 

 

And finally the graph,

GraphGraph

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please advise . I tried hit and trial with DAX queries but unable to reach the requirement. Please help. Thanks for everyones patience

 

 

@Anonymous

The TAT can be calculated by a measure, but what is the "Action Date Month" in the expected output?

 

Last PS Actitivy Date = MAXX(FILTER('Table','Table'[Activity Name]="PS"),'Table'[Action Date])

Vendor Assignment Date = MAXX(FILTER('Table','Table'[Activity Name]="Vendor Assignment"),'Table'[Action Date])

TAT = IF(ISBLANK([Last PS Actitivy Date]),BLANK(),INT([Last PS Actitivy Date]-[Vendor Assignment Date]))

Capture.PNG

Anonymous
Not applicable

 

Hi,

When i used the same query as suggested by you, i am getting the difference in 4 digits for different request numbers, but total in a single digit. Please advise on how you got the difference in days in the TAT column.

reference.PNG

 

 

 

Can you please tell me how you calculated the field 'Time spent' from the 2 dates available in the fields before it. Thanks

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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