# Calculate days of open project until end of month

Hello to you all, I hope you can help me with a calculation I’m stucked in.

I want to calculate the sum of days a project is open depending on other columns at the time the user can select in a slicer.

Therefore I have two tables. A date table and a fact table. Both are connected via several inactive connectors (created, performed), an one active connector (closed).

In my fact table there are columns for ID, cost unit, created, performend, and closed.

The goal is as follows:

The User selects a Month in Year in a slicer (dates table).

Task: how many days a cost unit/ID is open - calculated to the chosen month (slicer)

Calculation:

Start date:

• column “created”

End date:

• when column “performed” is in the chosen month (slicer), than column “performed”

• when column “perfomed” is blank and column “closed” is in the chosen month (slicer), than column “closed”

• when column “perfomed” and column “closed” are blank, than End of Month of the chosen month (slicer)

So far I tried the code below.

``````Overdue closed in days =
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]) = MONTH(SELECTEDVALUE(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[performend], DAY),
IF(
MONTH('crm Tätigkeit_BI'[closed]) = MONTH(SELECTEDVALUE(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[closed], DAY),
DATEDIFF('crm Tätigkeit_BI'[created], ENDOFMONTH(Dates[Date]), DAY)))
),[DaysBetween]),
USERELATIONSHIP(Dates[Date], 'crm Tätigkeit_BI'[created] ) -- relationship needs to be set on calculated column "End of Month" -> cause we want to know, how many days the cost unit is not closed to the End of Month (chosen month in slicer)
)
``````

Unfortunately I’m not able to set the relationship from a “real” column in a table to the calculated column “End of Month”. But without, the calculation delivers the wrong numbers.

Do you have any ideas how to solve this.

Hi,

Thanks,
Anurag

Hi Anurag,

Would be nice, if you / one could help.

Danilo
FSM_Master_test_v1.0.pbix (5.5 MB)

1 Like

Bumping this post for more visibility.

Hi @d.mesch - I have tried to come with a Solution, hope this is helpful.

1. Removed Relationship b/w Date table and fact table
2. Modified the existing DAX formula as below i.e. to remove UseRelationship. Final Filter condition is to ensure only those records are considered where closed date is b/w Selected month from slicer or is blank. You may need to modify it as per your requirement.

Note: Same can be used for other measures as required.

``````   # Overdue closed in days =
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]) = MONTH(MAX(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[performend], DAY),
IF(
MONTH('crm Tätigkeit_BI'[closed]) = MONTH(MAX(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[closed], DAY),
DATEDIFF('crm Tätigkeit_BI'[created], ENDOFMONTH(Dates[Date]), DAY)))

),[DaysBetween]),filter('crm Tätigkeit_BI',('crm Tätigkeit_BI'[closed] >= min(Dates[Date]) && 'crm Tätigkeit_BI'[closed] <= max(Dates[Date] )) || isblank('crm Tätigkeit_BI'[closed])))
``````
1. Seems to be getting correct values as shown below as per requirement.

Do let me know if your requirement is something different with an example may be in Excel and I will check further.

Thanks
Ankit J

1 Like

Thank you so much for that detailed answer @ankit

We hope this helped you @d.mesch

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Hi @d.mesch, we’ve noticed that no response has been received from you since September 29.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @ankit - since I’m on vacation this week, I can’t try your solution until next week. Early next week I will get back to you as soon as possible to thank you for the solution or to make further adjustments. Thanks so far in advance.

Danilo

Hello @d.mesch hope you had a great vacation. Just following up if you were able to try try the suggestion given above?

Hey @ankit ,

thanks for you great help. I have adjusted your formula a bit. So that really all values of the month selected by slicer are evaluated, I have inserted another IF condition on the column created.
Furthermore, the final filter on the column closed leads to wrong results, which is why I replaced it with EndOfMonth.

Here is the revised code.

``````# Overdue closed in days 2 =
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]) = MONTH(MAX(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[performend], DAY),
IF(
MONTH('crm Tätigkeit_BI'[closed]) = MONTH(MAX(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], 'crm Tätigkeit_BI'[closed], DAY),
IF(
MONTH('crm Tätigkeit_BI'[created]) = MONTH(MAX(Dates[Date])),
DATEDIFF('crm Tätigkeit_BI'[created], ENDOFMONTH(Dates[Date]), DAY),
BLANK()
)
)
)
), [DaysBetween]),
filter('crm Tätigkeit_BI',( [EndOfMonth] >= min(Dates[Date] ) && [EndOfMonth] <= max(Dates[Date] ) ) /*|| isblank( 'crm Tätigkeit_BI'[closed] ) */ )
)
``````

Unfortunately, the formula works only, with inactive connections / non-existent connections between FactTable and date table. Do you have an idea how to rewrite the formula so that it works also with an active connection? Or do I need to set up two date tables?

The overall construct of the PowerBI evaluation includes many other data and evaluations, so I can’t just cut the connections.

Danilo

Bumping this post for more visibility.

Hi @d.mesch - Try below formula. Giving same results as other but with connection b/w Date and Fact.

``````# 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))
``````

Thanks
Ankit J

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Hi @d.mesch, we’ve noticed that no response has been received from you since October 12.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hello @ankit ,

thx for your help. It was very welcome and precious for me.
I didn’t know, that I can use the Date itself within an expression of maxx. So thanks for learning twice in that topic.

Danilo

1 Like

Hello @ankit ,

one more question. Sometimes it seems that the formula for the total is calculating incorrect.

I tried to solve this by anoher Sumx, but unfortunatelly I didn’t get it work.

Could you have a look and maybe a solution for me?