How to create calender table and use in one dataset for datediff caluculation

Hi Team,
We are migrating power bi reports into quick sight, in power bi report the team has used dates(seperate set of dates) dataset and actual dataset in one visual, in that they are calculating datediff between date column of dates dataset and created date column of actual dataset. so how we can achieve same in our quick sight.

Hi @Shruthi - Thanks for the question. Can you please give more details on the date table in Power BI. How the date table join with data date table? If possible, please share some sample data and provide the input and output from power bi side. This will help in exploring the solution in QuickSight.

Regards - Sanjeeb

1 Like

The date table is containing the following dates
The actual data set contains application name and created date column, in Power BI they are calculating
1.Numerator as Distinct count of application names where, Datediff of above date column and created date column>0 and some column is true.
2.Denominator as Distinct count of application names where, Datediff of above date column and created date column>0 .
3.We are calculating percentage of numerator and denominator.
4. Here they didn’t join the two tables.
They are showing alternate months as X-axis.
So how can we achieve the same in Quick sight?

Hi @Shruthi
How did the know on what date the datediff is calculated if they didn’t join the table an a common column?

1 Like

In powerbi we can take column from any dataset right, so they have taken date column from date table and calculated datediff, they are finding datediff (day) of each created date I am also wondered how it is calculating

But what is the datediff telling you from a business point of view? Is it how many days ago “x” was created?

1 Like

This calculation is for atleast 365 days.

Sorry @Shruthi but i still dont get it.
Could you share a sample where we can see the datediff result as well as the created date.

we have created date-range from=2019-12-02 to=2023-12-26 in main_dataset and date table have dates like as below
nov 30 2022, dec 31 2022, jan 31 2023, mar 31 2023, april 30 2023, may 31 2023,june 30 2023,july 31 2023, august 31 2023, sep 30 2023, oct 31 2023, nov 30 2023

we have to take date diff of each created date column values and date column of date table
like dateDiff(main_dataset[created], max(date[dates]), ‘DD’)

So you calculate the date diff for every month. From a report point of view are you looking at every month to see the diff e.g. 30 Nov = 20, 31 Dec = 51 etc? Or is it always a fix month you are looking at?

I don’t know exactly but we have to take that datediff>=0 condition in numerator and denominator to calculate %application key

Mh. You could create a date table outside of QuickSight or use

In combination of a fix date (like now() )
and calculate the diff.

Kinda depends on the business requirement.

I know this type of calculation but my question is how to calculate datediff between each date of created date and date column date value

Hi @Shruthi
did you find a solution.

Hello @Shruthi, since we have not heard back from you, I will archive this topic. If you still need guidance on this issue, please post a new topic in the community. That will ensure you are at the top of the priority list to receive a response from one of our QuickSight experts. Thank you!