cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
null
Helper V
Helper V

datediff calculation

I want to create a calculation a gap between two date columns and sums up as numerator to be divided by another column, it seems datediff not work in calculation, so how can I calculation days of gap between twe date column in calculation?

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@null you haven't showed enough about what you want to get. Anyway I wrote some options, let me know if one of them worked:

 

V1A = 

SUMX(
	'adshrs_demand_detail_d',
	('adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]) / COUNT('adshrs_demand_detail_d'[request_headcount])
	)


V1' = 

SUMX(
	'adshrs_demand_detail_d',
	('adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]) / COUNTROWS('adshrs_demand_detail_d')
	)

V2 =
DIVIDE(
	SUMX(
		'adshrs_demand_detail_d',
		'adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]
	),
	COUNT('adshrs_demand_detail_d'[request_headcount])
)


V2' =
DIVIDE(
	SUMX(
		'adshrs_demand_detail_d',
		'adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]
	),
	COUNTROWS('adshrs_demand_detail_d')
)

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

4 REPLIES 4
SpartaBI
Community Champion
Community Champion

@null you can just do date2 - date1 for these use cases in general.

You probably need to wrap it with more logic, but can't say nothing with the info you gave 🙂 If you don't succeed please share a sample data (copt paste a table here) and the result you want to get

this is the data and I want the sum(date1-date2) as numerator

 

SpartaBI
Community Champion
Community Champion

@null you haven't showed enough about what you want to get. Anyway I wrote some options, let me know if one of them worked:

 

V1A = 

SUMX(
	'adshrs_demand_detail_d',
	('adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]) / COUNT('adshrs_demand_detail_d'[request_headcount])
	)


V1' = 

SUMX(
	'adshrs_demand_detail_d',
	('adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]) / COUNTROWS('adshrs_demand_detail_d')
	)

V2 =
DIVIDE(
	SUMX(
		'adshrs_demand_detail_d',
		'adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]
	),
	COUNT('adshrs_demand_detail_d'[request_headcount])
)


V2' =
DIVIDE(
	SUMX(
		'adshrs_demand_detail_d',
		'adshrs_demand_detail_d'[date2] - 'adshrs_demand_detail_d'[date1]
	),
	COUNTROWS('adshrs_demand_detail_d')
)

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Tahreem24
Super User
Super User

@null , I wonder how DateDiff is not working. Can you share the sample data and required output in order to better understand your scenario.

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors