Trying to find the % between bid response dates

[Anonymous]

I'm trying to calculate the percentage between when a bid was submitted and the response date.

This is a mock up sample of my data. What I'm trying to do is find the percentage between the date a bid was submitted and the bid response date. I tried using DATEDIFF but getting errors.. struggling with this. Appreciate your feedback.

ReqID	State	Product	Bid Status	Bid Amount	Bid Submitted	Bid Response	Bid Approved
10001	Alabama	Constructions	Awarded	$21,000	7/14/2020	10/4/2021	3/1/2023
10002	California	Bridge/Tunnels	In Progress	$15,000	5/5/2019	8/19/2022
10003	New York	Wastewater Treatment	Awarded	$900,000	2/1/2018	3/2/2020	4/2/2023
12002	Missouri	Constructions	Bid	$821,235	11/1/2020	5/1/2021
12004	Illinois	Constructions	Bid	$35,000	3/1/2019	12/31/2023
10005	Montana	Bridge/Tunnels	In Progress	$66,000,000	5/1/2018	4/30/2023
21002	Utah	National Parks	Awarded	$852,147	10/1/2020	1/11/2021	1/2/2024
10008	Utah	Wastewater Treatment	Awarded	$90,000,000	10/1/2020	9/11/2021	1/2/2024
210009	Connecticut	Roads/Highways	Awarded	$75,000	5/4/2023	6/4/2023	7/4/2024
10010	Kansas	Constructions	Bid	$654,000	3/1/2019	12/31/2023
10020	Iowa	Roads/Highways	Bid	$125,840	11/30/2021	6/6/2023
21010	Indiana	Roads/Highways	In Progress	$90,000	10/2/2019	6/6/2023
21012	Michigan	Wastewater Treatment	In Progress	$85,000	5/4/2023	3/4/2024
21015	Wisconsin	Bridge/Tunnels	Awarded	$21,000	1/2/2020	5/2/2023	12/1/2023
10006	Wyoming	National Parks	Awarded	$1,500,000	7/14/2020	9/3/2022	12/2/2024
31002	Wyoming	Bridge/Tunnels	Awarded	$6,000,000	7/14/2020	9/3/2022	1/2/2024
10006	Wyoming	Bridge/Tunnels	Bid	$1,500,000	8/4/2021	12/4/2021
21018	Alabama	Bridge/Tunnels	Bid	$75,000,000	9/9/2021	3/1/2024

[Anonymous · Accepted solution]

Hi  @Anonymous ,

May I ask if you want to find the percentage of the interval date of each row of [Bid Submitted] and [Bid Response] to the sum grouped by [Bid Status]?

Here are the steps you can follow：

Calculated column:

Day_Column =
DATEDIFF(
    'Table'[Bid Submitted],'Table'[Bid Response],DAY)

column_Percentage =
var _groupall=
SUMX(
    FILTER(ALL('Table'),'Table'[Bid Status]=EARLIER('Table'[Bid Status])),[Day_Column])
return
DIVIDE(
    'Table'[Day_Column],_groupall)

Measure:

Day_Measure =
DATEDIFF(
    MAX('Table'[Bid Submitted]),MAX('Table'[Bid Response]),DAY)

Measure_Percentage =
var _groupall=
SUMX(
    FILTER(ALL('Table'),'Table'[Bid Status]=MAX('Table'[Bid Status])),[Day_Measure])
return
DIVIDE(
    [Day_Measure],_groupall)

If I have misunderstood your meaning, please provide your desired output.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

percentage of what? You are dealing with date differences, so not clear how you want to arrive at percentages.