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

d.mesch · October 21, 2021, 9:08am

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?

Thanks in advance
Danilo

FSM_Master_test_v1.0 - w Connection.pbix (5.5 MB)

Harsh · October 21, 2021, 9:54am

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 = 
ADDCOLUMNS(
    _Cross_Joins ,
    "@Totals" , 
    [# Overdue closed in days] )

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

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

RETURN
_Results