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

 

 

 

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

     

3 Replies

  • 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