Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis 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?
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |