Cumulate between two dates in same table, USERELATIONSHIP needed for the same DAX

[FatherTheWizard]

Hi,

I tried 4 different topics here but could not solve the issue.

Target ( I have Ticketing data where One row = one ticket):

- Cumulate count of [ticket_id] for each day between [due_by] +1 to [resolved_at] -1 -> all this data in the same table (fact) Tickets

- Use USERELATIONSHIP from [due_by] to date table (table name (dim) Date, column date).

I have ticketing data where one row is one ticket and important date columns are due_by and resolved_at. I also need to use USERELATIONSHIP from due_by to date column in date table to get the chart working (=using due_by as the date dimension in an indirect way).

How to get the cumulating working? I seemed to have odd issues when applying the userelationship included so that is another problem.

Example of target to show for one ticket:

Date		due_by		resolvet_at		ticket_id		DAX for cumulative count
14.1.2021		14.1		17.1		example1		0
15.1.2021		14.1		17.1		example1		1
16.1.2021		14.1		17.1		example1		1
17.1.2021		14.1		17.1		example1		0
18.1.2021		14.1		17.1		example1		0

[Anonymous · Accepted solution]

@FatherTheWizard 

First of all, you need to change the all those date data into actual Date Type column.

You can create a flag column first for the row in between the due date and resolve date. If all information are from the same table, you don't need userelationship to activate the date in the Dim date table.

flag = IF([Date] in CALENDAR([due_by],[resolvet_at]),1,-1)

Column = CALCULATE(SUM('Table'[flag]),FILTER('Table',[Date]<=EARLIER([Date])))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.