datediff calculation

[Anonymous]

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?

[SpartaBI]

@Anonymous 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

[Anonymous]

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

[SpartaBI · Accepted solution]

@Anonymous 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')
)

Showcase Report – Contoso By SpartaBI

      

[Tahreem24]

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