Find Difference between two dates
We need to find difference between two date dimensions present in our model.
Is it possible to do it using formulate?
Hi Aryat Srivastava ,
Could you please provide some more details so we could help you?
What data source are you using? (SSAS, pyramid in-memory, Sql Server, Oracle...)
How do you data and model look? Could you maybe add an image with a sample of a grid with your data and what you would want to calculated?
I could say from a high level point of view, using PQL (for non MDX data sources) you can use the DateDiff function:
And you may also also need to use the Caption function to get the date from your members:
So maybe something like DateDiff([dim].[date1].caption, [dim].[date2].caption, "dy")
depending on what is your final goal, I would use the SQL-Function DateDiff before/during the import of the data into the analytic model. The result (e.g. the difference between the order date and the invoice date in days) would then be a measure.
The most difficult question is, however, what ist the relevant date for the fact table then. Is it the order date or is it the invoice date...? ;-)
There is a Pyramid function DateDiff() that can be using in formulate to calculate the difference between two dates in various units. See
for further information.
Note that this calculation only makes sense when the Dates are contained within the same table, for example in the FACT /MEASURES Table / Dimension, i.e. there is a 1:1 correspondence between the two dates.
Quite often, the base datekeys in the FACT table are hidden, exposed in the associated Date dimension attributes. Make sure you can see the datekeys in the same table to be able to create the calculation.
Hope this helps,
If you are using a SQL-based model (i.e. IMDB, SQL, Oracle, Redshift), you can accomplish this with the help of a function called StringToDate. Here is an example from a Claims model:
DateDiff(StringToDate([Fact_Claims].[ClaimServiceDateThrough].CurrentMember.Caption, "yyyy-MM-dd"), StringToDate([Fact_Claims].[ClaimPaidDate].CurrentMember.Caption, "yyyy-MM-dd"), "dy")
This is great information on identifying a date difference. Is there anyway to see the date difference as a dimension? We are calculating the age of automobile drivers and want to see it as a dimension, since adding up ages as a measure doesn't make much sense.
I know that we can do both measures and dimensions of of a single data model attribute. However, I’m wanting to calculate the age using a date difference between today and their birthdate and show it as a dimension value. Right now I can calculate the age but it can only be represented as a measure.