Challenging question - determine current state by month

[aaa12]

Hello everyone, 

I have a challenging question. I have the table as below, with open date, closed date, and status...

My goal is to show a count of items that remain open during each month and create a graph like below:

So far the best approach I can think of is to subtract the closed date from the open date and create a matrix with a value of 1 for each month in between (screenshot below). BUT I can't figure out how to do that in DAX. Can anyone guide me here? or recommend a better solution?

[v-xinruzhu-msft]

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @aaa12 , based on your descriotion, you want to calculate the open items based on each month, you can refer to the following solution.

Sample data 

And i have a Calendar table, there is a 1:N relatinship relationship between tables, the key columns are open column in data table and date column in calendar table.

1.In data table create a calculated column

ClosedDate = IF([Closed]<[Open],MAXX('Calendar',[Date]),[Closed])

2.Create a measure

Open_count =
VAR _add =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Table',
            "Flag",
                VAR a =
                    CALENDAR ( [Open], [ClosedDate] )
                VAR b =
                    VALUES ( 'Calendar'[Date] )
                RETURN
                    COUNTROWS ( INTERSECT ( a, b ) )
        ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[Open], NONE )
    )
RETURN
    COUNTROWS ( FILTER ( _add, [Flag] > 0 ) )

Then create a visual, and put the date column and the measure to the visual.

Output

Best Regards!

Yolo Zhu

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

[aaa12]

hello - thank you for the response. Unfortunately the totals were different than actuals using the proposed solution. I went with somewhat of an inelloquent solutions by creating separate columns to evaluate each time period and then graphing it together. It works but as mentioned before, it's not so elloquent. 

Thank you again. 

[aaa12]

calling top authors for guidance :)! @lbendlin ; @AlienSx ...have you seen a similar use case before?

[lbendlin]

1. Do not use fake dates. Leave the close date empty and use COALESCE or a filter.

2.  You are showing by month.  Does it mean you want to count which tickets were closed on any day of the month? 

3. use a calendar table.

[aaa12]

@lbendlin : Thank you...I wasn't able to reproduce it...

1. Do not use fake dates. Leave the close date empty and use COALESCE or a filter. (DONE)

2.  You are showing by month.  Does it mean you want to count which tickets were closed on any day of the month? (YES, just by month)

3. use a calendar table. (DONE)

My results....it seems the open and closed totals cancel each other out...

My measures:

Closed

Open

Running Total

My modeling relationships: