Date Calculation/function for the time/date

[poko]

Good afternoon community,

Can anyone help me to make a calculation in PowerBI for this event in the red circle? I got calculation for Total Lead Stand function.

more less its should be Total 782 and around 321.

My result so far is below:

In Blue Circle, it's correct, but not in red circle...I created calculations and calendar calculations but it must be something wrong...it just doesnt cooperate each other. I attached pbix file please , have a look into my file and help me to find error. Why it doesnt count correctly with calendar?.. Iam hopeless I tried many things but no luck.

[MFelix]

Hi @poko ,

Believe that the question is regarding the blank values since there is no date is not possible to compare, if you chech the 192 correspond to the ones that have dates and the other one to the ones that do not have dates.

Can you confirm what us exactly you want to calculate?

I assume you want all the cases that have a deifnitive date that is higher than 6 years is that correct?

[poko]

Yes sure with the dates of course I know there are some empty cells in date column. Please if you can help with the ones that have dates it would be great.

[MFelix]

@poko,

So you want to check the ones that have a judgement date greater than x years is they it?

Is the number of years dynamic or fixed?

Also is the difference for the number of year to be done for current date or any value in a slicer? 

[poko]

Good morning MFelix,

"So you want to check the ones that have a judgement date greater than x years is they it?" YES, please.

"Is the number of years dynamic or fixed?" What you mean by dynamic? They fixed, they don't change, always the same ,just add new ones after a time.

"Also is the difference for the number of year to be done for current date or any value in a slicer?" yes, when a user set a day today in the slicer, the table must show values and dates 5 years and over from the current date.

Thank you for support really appretiate it.

[MFelix · Accepted solution]

Hi

@poko ,

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

[poko]

Thank you very much for this solution it works like a charm. I addapted your pbix and works as it should be inclusing dynamic slicer.