Date Calculation/function for the time/date

Hi

 

Anonymous ,

 

When I refer dinamic is if you want to have a slicer with the numbers of year for example you select 10 and the calculation is done for 10 years instead of 5.

 

Add the following measure:

CasesMoreThan5Years = 
	VAR _maximumdate = MAX(Calendar1[Date])
		VAR _temptable = ADDCOLUMNS(
			'CecCaseDetail',
			"@yeardiff", DATEDIFF(
				CecCaseDetail[DefinitiveJudgmentDate],
				_maximumdate,
				YEAR
			),
			"@leading", [LeadingTotalStand]
		)

		RETURN


			SUMX(
				FILTER(
					_temptable,
					[@yeardiff] >= 5
				),
				[@leading]
			)

This will give you the expected result in this case the 192.

If you want to have a dinamic value just do the following:

• Add a numeric parameter table with values from 0 to 50 (or whatever other value you want)
• change your measure to:

CasesMoreThan5Years = 
	VAR _maximumdate = MAX(Calendar1[Date])
		VAR _temptable = ADDCOLUMNS(
			'CecCaseDetail',
			"@yeardiff", DATEDIFF(
				CecCaseDetail[DefinitiveJudgmentDate],
				_maximumdate,
				YEAR
			),
			"@leading", [LeadingTotalStand]
		)

		RETURN


			SUMX(
				FILTER(
					_temptable,
					[@yeardiff] >= 'Number of Years'[Number of years selection] //5
				),
				[@leading]
			)

You can see the change is on the number of years that now is not hard coded but uses the measure Number of years selection:

 

I haven't renamed the measure but it's not possible to make the name dynamic just renamet it to something like CaseMorethanXyears for example

See attach file

 

https://filetransfer.io/data-package/FDaXH6I9#link