Calculate days of open project until end of month

d.mesch · September 27, 2021, 10:39pm

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.

Anurag · September 28, 2021, 4:52am

Hi,

It will be helpful for me and other to help you quickly if you provide us your working pbix file .

Thanks,
Anurag

d.mesch · September 28, 2021, 1:02pm

Hi Anurag,

thx for your reply. I have revised my challenge to be more specific about the goal.
Additionally I have uploaded a sample file.

Would be nice, if you / one could help.

Thx in advance
Danilo
FSM_Master_test_v1.0.pbix (5.5 MB)

EnterpriseDNA · September 30, 2021, 8:37pm

Bumping this post for more visibility.

ankit · October 3, 2021, 10:13am

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

Removed Relationship b/w Date table and fact table
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])))

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

EnterpriseDNA · October 4, 2021, 11:22pm

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.

EnterpriseDNA · October 6, 2021, 1:38am

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.

d.mesch · October 6, 2021, 8:23am

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

EnterpriseDNA · October 10, 2021, 11:22pm

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

d.mesch · October 11, 2021, 8:25am

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.

Thanks in advance
Danilo

EnterpriseDNA · October 14, 2021, 12:18am

Bumping this post for more visibility.

ankit · October 17, 2021, 9:11am

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

EnterpriseDNA · October 18, 2021, 12:35am

Hello @d.mesch, just following up if the response from @ankit help you solve your inquiry?

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.

EnterpriseDNA · October 19, 2021, 12:14am

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.

d.mesch · October 19, 2021, 11:21am

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

d.mesch · October 21, 2021, 7:58am

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?

Thx in advance
Danilo

EnterpriseDNA · October 21, 2021, 8:07am

Hi @d.mesch, kindly create a separate post for the last question that you posted. We will be tagging this post as solved.

Thanks