Forum Discussion

Rehab_hassan's avatar
Rehab_hassan
Frequent Visitor
7 years ago
Solved

create a measure to calculate time difference

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 ...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

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's avatar
Rehab_hassan
Frequent Visitor
7 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    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