Forum Discussion
Count completed tasks by planned vs actual date
Hi tgprasad85
There is no error in your measures. I test them with some data and get the result correctly.
The date column shows blank because all the dates in Tasks table are not mapped to existing dates values in Date table. Your Date column in Date table is of datetime type, and I guess all of the date columns in Tasks table are of datetime type too, although they show in a date format. Their time values still exist and influence the result. For example, 3/11/2021 7:00:00 is not equal to 3/11/2021 12:00:00 because they have different time values.
If you want to achieve your goal, you need to remove the time values and compare only on date values. If you don't need to use time values in your report, you can change these columns to Date type to remove time values directly.
If you need time values for other use in your report, you can try Amit's suggestion to add four new date columns which only contain date values from original columns, and build relationships based on these new columns and date column in Date table.
Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Super Usertgprasad85 , for that you need to create a date table and joined both dates( after removing the timestamp) with the date of the date table. One relation will be inactive that you can activate using use relationship
Example of new column date from date time
Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))
How to handle multiple dates
Now diff between the measures created with both dates will be variance
Hi Amit,
Thank you for the reply. I tried with the solution provided. my issue is the dates get totaled
Tasks table
Date Table
Measures created
The table does not give me the count of tasks at date level. Please advice
Thank you!
Table relationship screenshot
One active relationship to Start date and rest are inactive relationships
v-jingzhangCommunity Support
Hi tgprasad85
There is no error in your measures. I test them with some data and get the result correctly.
The date column shows blank because all the dates in Tasks table are not mapped to existing dates values in Date table. Your Date column in Date table is of datetime type, and I guess all of the date columns in Tasks table are of datetime type too, although they show in a date format. Their time values still exist and influence the result. For example, 3/11/2021 7:00:00 is not equal to 3/11/2021 12:00:00 because they have different time values.
If you want to achieve your goal, you need to remove the time values and compare only on date values. If you don't need to use time values in your report, you can change these columns to Date type to remove time values directly.
If you need time values for other use in your report, you can try Amit's suggestion to add four new date columns which only contain date values from original columns, and build relationships based on these new columns and date column in Date table.
Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.