This video explains exactly what I need: https://www.dundas.com/resources/off-the-charts-tips-from-an-expert/elevate-your-dashboards-reports/extend-your-data-with-dundasscript
But HOW am I supposed to do to get dates displayed as measures (last column “Start Date”)
Hi Sarah,
May I know why you want to add it specifically as a measure?
Hi Shashank,
I am using a stored procedure to retrieve the data.
I need something like this:
It is a Project Dashboard, where I need to compare dates (start date, end date, etc.) between 2 scenarios.
I also need to calculate the difference in days between those dates. (I am also struggling with this calculation.
Thanks for your help
Hi Sarah,
If you’re working with a MS SQL DB, then you can use this fucntion for doing the calculation:
CREATE FUNCTION [dbo].[GetNumberOfDaysBetween2Dates]
(
– the format of the dates must be dd/mm/yyyy use convert(date,TheDateToTransform,103) for this
@TheDateB date,
@TheDateA date
)
RETURNS int
AS
BEGIN
– Declare the return variable here
DECLARE @Difference int
SET @Difference = DATEDIFF(day,CAST(@TheDateB AS NVARCHAR(10)),CAST(CONVERT(date,@TheDateA,103) AS NVARCHAR(10)))
-- Return the days between @TheDateB and @TheDateA
RETURN @Difference
END
For having the number of months just replace “day” by “month” as first argument of the DATEDIFF, and if you want to have the number of years just replace “day” by “year”.
For passing the arguments in the correct format just use convert(date,TheDateToTransform,103), it works if the date is in a string format or a datetime format.
I hope this helps,
Olivier
Hi Sarah,
Well, I don’t do anything as complicated as Olivier’s approach, nor do I follow how a store procedure here would be a gain. As for me, I keep copies of C# formulas I copy and paste into the “Calculated Transforms”. Here’s an example of a C# code semi-tailored for your business example:
First, my ‘days different" is 32 bit integer’, and I’m NOT attracted to negative counts of days:
if ($Date A$ <= $Date B$) {
return 0;
} else {
DateTime date1=$Date A$;
DateTime date2=$Date B$;
return (date1-date2).Days;
}
Call me a nut, but I like simple. But maybe enough of a seed to get you over the ‘struggle’?
Best regards,
Steve Glaeser
Hi Steve,
The SQL function (it’s not a stored procedure), can be called in the query built in the DataCube, and returns all the kind of results, be them positive or negative ones.
For completing your C# code I would add:
if ($Date A$ == $Date B$) {
return 0;
} else if ($Date A$ < $Date B$) {
Datetime date1 = $Date B$;
Datetime date2 = $Date A$;
return (((date1-date2).Days) * -1);
} else {
DateTime date1=$Date A$;
DateTime date2=$Date B$;
return (date1-date2).Days;
}