monthly totals of two columns with different dates

[FraMeh]

Hi,

I have a table inculing these two columns:

column1: the date when the item was created

column2: the date the item was finalized

Now I would like to display the monthly totals of the Items in process (column1<= Last Day of Month < column2) so I get the number of items which where already created at the end of the certain month, but not yet finalized. 

I'd like to create a bargraph that shows me this amount for each month.

How can I do this?

Thank you in advance for your help, I stuck in this task since a long time

[v-sihou-msft]

@FraMeh

I have the same question as @ImkeF. Does one item span multiple months?

If not, you can use a full calendar date table, build the relationship with this Table on column1, create a calculated column to tag if current item is finished in current month.

IsFinished in Current Month=
IF(Table[column1] <= EOMONTH(Date[Date],0) && Table[column2] > EOMONTH(Date[Date],0), 1 ,0 )

Then count the items where IsFinished in Current Month is 1.

If one item can span multiple months, it will be more compliated. You need to have each item cross join your data table. Then compare if current date within the range between column 1 and column 2. If count of "Within Range" equals the count of days within current month, this item will be counted as 1, otherwise it will be counted as 0.

Regards,

[FraMeh]

Thanks for your reply,

how can I do the cross join for each item?

[ImkeF]

Will there always be just one month end in between or could they also span multiple months?

[FraMeh]

Hi ImkeF,

they can span multiple months.

[ImkeF]

You might find a suitable solution here: http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/