create a measure to calculate time difference

[Rehab_hassan]

hello all,

I am getting my data from the analysis services database where adding coloums option is greyed out (I amnot sure if this is my company limitation) and the only option I have is adding a measure.

I need to create a measure that captures the time difference between two dates (Opportunties[changed on] ) and (Opportunties[Created on])in days.

and another formula to calculate the difference between (Today) and (Opportunties[changed on])

I tried many formulas but unfortunately it didnt work.

-->delta = DATEDIFF(FIRSTDATE('opportunties'[Created On]),LASTDATE('opportunties'[Updated On]),DAY)

-->Delta_Lead = DATEDIFF('lead'[Changed On],NOW(),DAY)

-->DATEDIFF([today],LASTDATE('Dim_Leads'[Updated On]),day)

where today is another measure created by me as (today=TODAY())

any advice will be apperciated.

Regards,

Rehab

[MFelix · Accepted solution]

Hi @Rehab_hassan ,

On the analisys service is not possible to add columns, this is not a limitation from your company but is because of the model type. Analisys services are "closed" databases and you cannot change the model as you want since doing it would break the analisys services results.

Regarding the question you are placing I'm assuming that the two dates you refer are columns on your model you could try to use something like this:

delta = DATEDIFF(MAX('opportunties'[Created On]),MAX('opportunties'[Updated On]),DAY)

Be aware that being a analisys service there another limitations like some DAX formulas not being available, some calculations are not quite made in the same way and you need to make work arounds.

Regards,

MFelix

[Rehab_hassan]

This is very very helpful 🙂

can I ask you , what if I want to create a measure to extract the quarter and another measure to extract the year from the (created on) coloum , how to do that?

this one is only working as a coloumn , what about creating a measure?

Quarter = 'opportunties'[Created On].[Quarter]

[MFelix]

Hi @Rehab_hassan ,

You need to create something like this:

Month = MONTH(MAX(Opportunities[Created On])))



Quarter =
SWITCH (
    TRUE ();
    MONTH ( MAX ( Opportunities[Created On])) ) <= 3; "Q1";
    MONTH ( MAX ( Opportunities[Created On]) ) ) <= 6; "Q2";
    MONTH ( MAX ( Opportunities[Created On])) ) <= 9; "Q3";
    "Q4"
)

Regards,

MFelix