Measure to keep count of tasks not completed each month

[bullozah123]

Hello, 

I'm in need of some help. I need to do a visualization that will count the comulitive amount of tasks left behind. 

So for example if I have: 

		created	resolved
	task1	01/01/2022	02/01/2022
	task2	02/01/2022	01/02/2022
	task3	03/01/2022	02/02/2022
	task4	04/01/2022
	task5	05/02/2022	06/02/2022
	task6	03/03/2022	04/03/2022
	task7	05/03/2022
	task8	08/04/2022

The final result would be: 

	transact	open	close
jan	0	4	1
feb	3	1	2
mar	1	2	1
apr	2	1	0

In january no transacted were received since there is no previous data. In february 3 transacted tasks were received (task2, 3 and 4). In march, 1 task was received (task4) since it hasn't been resolved in this month. Finally april received two transitioned tasks (task4 and 7) that are the tasks unsolved from previous months.

I was able to do the open and close but not the transact (tasks that transacted to the next month). 

At the moment I have a Dimension Table for Dates that connects to the open date, on the usual relationship of one to many to my fact table. I've also connected the resolved. 

Hope someone can help me. Thank you. 

@amitchandak @olgad @FreemanZ 

[bullozah123]

Hi, 

Thanks for the help! I feel I'm getting closer. Unfortunately this is still not working. I believe this is due to this part: 

EOMONTH('Table'[resolved],0)=EOMONTH(max('Table'[created]),0)

Could you please explain what you are trying to compare here? I'm new to powerbi. 

This is the results that I'm getting with your measure: 

As you can see there were 59 tasks that went from february to march, but the measure shows 167... 

Thanks for the help! 

[olgad]

Hi, EOMONTH is just making end of the month out of the date, so i can compare the months 31/1/2023 of the resolved with 31/1/2023 of the created, disregarding the day dates. 
Do you want to ping me your file in private messages? I can take a look.

[olgad · Accepted solution]

Hi, 
i resolved it without taking the calendar/date table may be you can work further with that to adapt to your case,  if you dont succeed, please let me know which relationship you have between the resolved and the date table, inactive? then may be I will take another look.

Cumulative Total =

var maxdate=Date(Year(max('Table'[created])), Month(max('Table'[created])), 01)

var resolved=Date(Year(max('Table'[resolved])), Month(max('Table'[resolved])), 01)

RETURN

CALCULATE(

    Count('Table'[task]),

    FILTER(

        All('Table'),

       ('Table'[created]<maxdate && 'Table'[resolved]=blank()) ||  'Table'[created]<maxdate && EOMONTH('Table'[resolved],0)=EOMONTH(max('Table'[created]),0)

    ))

[v-yangliu-msft]

Hi  @bullozah123 ,

Here are the steps you can follow：

1. Create calculated column.

Date =
CALENDAR(
    DATE(
        2022,1,1),
        DATE(
            2022,12,31))

2. Create calculated column.

Month =
FORMAT('Date'[Date],"mmm")

3. Create measure.

transact =
COUNTX(
    FILTER(ALL('Table'),
    FORMAT('Table'[created],"mmm")=MAX('Date'[Month])&&'Table'[resolved]<>BLANK()),[task])

open =
COUNTX(
    FILTER(ALL('Table'),
    FORMAT('Table'[created],"mmm")=MAX('Date'[Month])),[task])

close =
COUNTX(
    FILTER(ALL('Table'),
    FORMAT('Table'[created],"mmm")=MAX('Date'[Month])&&'Table'[resolved]=BLANK()),[task])

4. Result:

Best Regards,

Liu Yang

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

[bullozah123]

This didn't work as all transacted spaces appear as blank. But please review my question as I believe I've explained myself wrong. 

[JirkaZ]

You could tackle that with various approaches. 
Example - create a measure like so:

Transfered Tasks := 

SUMX('TasksTable', IF(ISBLANK('TasksTable'[resolved]), 1, 0))

[bullozah123]

The problem with this solution is that it doesn't attend to the creation date of the tasks. So for example, tasks that were created in January and only Closed in february won't show up as a transacted task from january to february because although the resolve date is not blank, they were not resolved in that month.