# Calculate days of open project until end of month - Part 2

Hello all,

in Topic Calculate days of open project until end of month you helped me to develop the correct formula for an analysis. Thx so far to @ankit for the final solution.

``````# Overdue closed in days 2 =

var MaxDate = maxx(VALUES(Dates[Date]),Dates[Date])
var MaxDateMonth = month(MaxDate)

return

CALCULATE( SUMX(
SUMMARIZE('crm Tätigkeit_BI', 'crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[performend], 'crm Tätigkeit_BI'[closed], "End of Month", ENDOFMONTH(Dates[Date]),
"DaysBetween",
IF(
MONTH('crm Tätigkeit_BI'[performend]) = MaxDateMonth,
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[performend], DAY),
IF(
MONTH('crm Tätigkeit_BI'[closed]) = MaxDateMonth,
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[closed], DAY),
IF(
MONTH('crm Tätigkeit_BI'[created]) = MaxDateMonth,
DATEDIFF('crm Tätigkeit_BI'[created], MaxDate, DAY),
BLANK()
)
)
)
), [DaysBetween]),ALL(DATES))
``````

Unfortunately, I only noticed after marking task Calculate days of open project until end of month as closed, that the total is calculated wrong.

I tried to solve this by another Sumx for the Total, but somehow I didn’t get it work.
May someone of you can help me with a correct calculating solution?

Danilo

Hello @d.mesch,

Thank You for posting your query onto the Forum.

Firstly, rather than using a flat file you’ll be required to create the dimension tables. In this case, I’ve created 2 dimension tables - “Typ Table” and “Typ 2 Table”. And created a relationship with the “crm Tätigkeit_BI Table”. Below is the screenshot of the new relationship status provided for the reference -

Now, to fix the totals below is the measure alongwith the screenshot of the final results provided for the reference -

``````Correct Totals =
VAR _Cross_Joins =
CROSSJOIN(
VALUES( 'Typ Table'[Typ] ) ,
VALUES( 'Typ 2 Table'[Typ2] ) ,
VALUES( Dates[Month Name] ) )

VAR _Virtual_Table =
_Cross_Joins ,
"@Totals" ,
[# Overdue closed in days] )

VAR _Remove_Blanks =
FILTER(
_Virtual_Table ,
NOT ISBLANK( [@Totals] ) )

VAR _Results =
SUMX(
_Remove_Blanks ,
[@Totals] )

RETURN
_Results
``````

I’m also attaching the working of the Excel as well as PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

2 Likes

Thanks @Harsh for your quick solution.

I hope that one day I can return it to you.

Danilo

2 Likes

Hello @d.mesch,

You’re Welcome.

I’m glad that I was able to assist you and you found it helpful.

And thank you so much for your kind words.

Thanks and Warm Regards,
Harsh