Shared time axis on 2 different dates

[Anonymous]

Hi,

Here is a sample :

ticket_number	creation date	closure date
ticket 1	02/01/2019	02/01/2019
ticket 2	05/01/2019	06/01/2019
ticket 3	10/01/2019	11/01/2019
ticket 4	15/01/2019	17/02/2019
ticket 5	20/02/2019

I would like to have this :

Month	created	closed	backlog
2019-01	4	3	1
2019-02	1	1	1

How can do to share the same time axis and calculate the rolling difference, please ?

Thanks,

Romain

[v-yuta-msft · Accepted solution]

@Anonymous ,

You can create calculate column using DAX like pattern below:

Created = CALCULATE(COUNT(Table1[ticket_number]), FILTER(Table1, MONTH(Table1[creation date]) = MONTH(EARLIER(Table1[creation date]))))

Closed = CALCULATE(COUNT(Table1[ticket_number]), FILTER(Table1, MONTH(Table1[closure date]) = MONTH(EARLIER(Table1[closure date]))))

  

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yuta-msft , that works for Created and Closed, do you have an idea how to calculate the rolling difference, please ?

Thanks

[v-yuta-msft]

@Anonymous ,

Could share more details/logic about "Rolling Difference" you mentioned?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.