date

Hi Anonymous ,

For this, I'd use a separate Datest able and create a relationship to Created Date and Complete Date columns with one being inactive. I would then use USERELATIONSHIP function to make use of an inactive relationship.

active and inactive (in dashes) relationships

sample formula

Total (USERELATIONSHIP) = 
CALCULATE ( [Total], USERELATIONSHIP ( Dates[Date], Data[Completed Date] ) )

sample result

Please see this pbix for your reference - https://drive.google.com/file/d/1fig8KTnWDnzL1ZkVjKUGmv9TYlE7xyZ8/view?usp=sharing 

Hi Dane,

Thank you for your reply, the problem here is the completed date is filtered out by created date. For ex: for the weekend starting 25 Jul 2022, the created number is 20(items created between 25th Jul to 31st Jul) similarly the completed items should show 25(Items completed between 25th Jul to 31st Jul). but the problem here is the completed items is considering only the items which got created on the above weekend only, but the requirement here is any items which got completed irrespective of the creation date must show. But unfortunately it is cutting the numbers. so it should not filter the created date . Can you please suggest

X axis should be week start date, I tried using common date table, it is not working. Please also guide which common date i should use in Slicer

Hi 

use USERELATIONSHIP as suggest is a good choice. You create a new Date table with fied name "Date", create relation field CreateDate with Date, then use USERRELATIONSHIP as
total = CALCULATE(SUM(),USERELATIONSHIP(column of "Date", column of "Complete Date))

But as Microsoft instruction, USERELATIONSHIP  have limitation if you define Row Level Security in your table.

For me, make it simple, I will create a new table of "Complete", include product and completedate, that mean you will have one table about createdate, one about complete date, 2 table related with an Date table, then you can filter as you want

Thank you so much for the response, it is working fine, On top of this I need one more help on Cumulative total for every weekend, I have created a separate table as summarized, however when I use the values in column chart and select the date range as last 2 months, i am not seeing the exact values as it is cutting the values. could you please help me on this