Accounting failures in regards to start date, without changing data back in time

[ChristianRHouen]

Hi all

I'm trying to measure the number of failed projects in regards to their start date, without having data changing back in times. My goal is to report a number monthly, that can be graphed over time.

I have 2 data tables; (1) calendar table (ordinary PowerBI calendar table with month, year, quarter etc.) and (2) a table of my projects start and fail date as seen below:

Here you can find the start date of the project, the fail date, the ID (unique) and whether the project failed in 6 months or less (1=yes, 0=no). The last being a calculated column.

The 2 data tabels are connected through start date (Project Table) to Date (Calendar Table).

I want a output that looks like this:

The idea is that I want to do monthly reporting of the percentage of projects that has failed in less than 6 months (row 5). To take it from the top:
(row1, jan) we started one project that later proved to fail within 6 months. We started 2 projects, but only 1 would later fail. This project failed in may, so when we reach may we go back and change this number i.e. it can change back in time.
(Row 2 - number of projects that were started during Last 6 (L6) months and failed in less than 6 months) we don't have any failed projects in january-april, and as such this number is 0. In May we register the fail, and number is changed to 1. We don't go back in time to change this number. In June, we still account the fail, as the project was started 6 months or less ago. In July, the project is 7 months old and we don't account for it any longer. Essentially, this is a 6 months running total of row 1, but without counting the fail before the actual fail date.
(Row 3 - number of projects started per month) We started 2 projects in january - this is independent of failure.
(Row 4 - Running total of started projects last 6 months) Taking a 6 months running total of row 3.
(row 5 - Row 2 divided by row 4.) This is the number I would like to report on a monthly basis.

It's a little complicated, but I hope you can help me out. As you can see from the status overview, it is only row 2 (marked in red) that I have not solved. The issue I found is that the 4 fields marked in yellow are incorrect in my version, as I can only seem to get a running sum to work.

I used the formula:

Failures_L06M =
   VAR pre6 =
       EDATE ( MAX ( DateTable[Date] ), -6 )
   VAR cure =
       MAX ( DateTable[Date] )
   RETURN
       CALCULATE (
           SUM([Failures_06MonthsOrLess]),
           FILTER ( ALL ( DateTable ), DateTable[Date] >= pre6 && DateTable[Date] <= cure )
       )

But as mentioned it does not give me desired outcome

[MFelix · Accepted solution]

Hi @ChristianRHouen ,

Check if the attach file is working measure is:

Failures_L06M =
VAR pre6 =
    EDATE ( MAX ( DateTable[Date] ); -6 )
VAR cure =
    MAX ( DateTable[Date] ) 
VAR temp_table =
    FILTER (
        ALL ( 'Data (2)' );
        'Data (2)'[DeployDate_InclDOA] >= pre6
            && 'Data (2)'[DeployDate_InclDOA] <= cure
            && 'Data (2)'[EndDate] <= cure
    )
RETURN
    SUMX ( temp_table; 'Data (2)'[Distance >6M] ) + 0

[MFelix]

Hi @ChristianRHouen ,

Believe that I understood you requirement but please check the file attach.

Failures_L06M =
VAR pre6 =
    EDATE ( MAX ( DateTable[Date] ); -6 )
VAR cure =
    MAX ( DateTable[Date] )
VAR temp_table =
    FILTER (
        ALL ( Data );
        DATESBETWEEN ( Data[Start Date]; pre6; cure )
            && Data[Fail Date] <= cure
    )
RETURN
    SUMX ( temp_table; Data[Failed in 6 months or less] ) + 0

I have used the sum of  Failed in 6 months but this can be adjusted.

[ChristianRHouen]

Thanks - it seems to work in the model you attached, however, when I use it on my own data I get the following error code: 

Error Message:

MdxScript(Model) (124, 9) Calculation error in measure 'External Measures'[ExtFailures_L06M_2]: A table of multiple values was supplied where a single value was expected.

Do you know what I'm doing wrong?

[MFelix]

Can you share your formula?

[ChristianRHouen]

Yes, sorry was just fixing the file (WeTransfer link)

https://we.tl/t-D2b3761h4K 

I basically uploaded my own data and tried the exact same formula.

[MFelix · Accepted solution]

Hi @ChristianRHouen ,

Check if the attach file is working measure is:

Failures_L06M =
VAR pre6 =
    EDATE ( MAX ( DateTable[Date] ); -6 )
VAR cure =
    MAX ( DateTable[Date] ) 
VAR temp_table =
    FILTER (
        ALL ( 'Data (2)' );
        'Data (2)'[DeployDate_InclDOA] >= pre6
            && 'Data (2)'[DeployDate_InclDOA] <= cure
            && 'Data (2)'[EndDate] <= cure
    )
RETURN
    SUMX ( temp_table; 'Data (2)'[Distance >6M] ) + 0

[ChristianRHouen]

It works - thanks! @MFelix 

Now it is, however, not filterable with other metrics. Is there also a solution for that?

I would like to be able to pick a few IDs and look at it - I also have additional categories in my other tables that I would like to be able to examine.

Is it possible?

[MFelix · Accepted solution]

Hi @ChristianRHouen ,

Change the ALL to ALLSELECTED and it will work correctly.

Failures_L06M =
VAR pre6 =
    EDATE ( MAX ( DateTable[Date] ); -6 )
VAR cure =
    MAX ( DateTable[Date] ) 
VAR temp_table =
    FILTER (
        ALLSELECTED ( 'Data (2)' );
        'Data (2)'[DeployDate_InclDOA] >= pre6
            && 'Data (2)'[DeployDate_InclDOA] <= cure
            && 'Data (2)'[EndDate] <= cure
    )
RETURN
    SUMX ( temp_table; 'Data (2)'[Distance >6M] ) + 0

File attach.

[ChristianRHouen]

Thanks a lot @MFelix - that worked.

Final question. I would like to show last months value as a KPI box, however, using my usual formula does not work on this formula.

My usual formula is 

LastMonth_Index = CALCULATE(Index_L6M],FILTER(DateTable, DateTable[IsLastMonth]=1))

Where 'Index' is calculated using previous formula as denominator, and IsLastMonth is a binary value column in date table indicating the last month from today as 1 and all others as 0.

[MFelix]

Hi @ChristianRHouen ,

The measure should give you the result based on the last date selected from the calendar table so if you filter the card by the columbn last month = 1 should give you the expected result.

No need to add additional calculations.

[ChristianRHouen]

@MFelix 

hmm - you're right, when I use cards, it displays correctly, however, if I use a matrix and select a Category, then it does not display correctly. In below example all numbers highlighted should be 0% - but in matrix it is >3000%.

Also, 

Also, do you have a suggestion to avoid the flatlining because it wants to include future date? I already tried removing the "+0" from the formula.

[MFelix]

Hi @ChristianRHouen ,

Sorry for the late response.

Just to clarify one thing this calculations you are placing on the vizualizations is not the same as the one I helped you earlier but is based on that calculation correct?

Try the following for the matrix:

Measure 2 = 
var scanner_selected = SELECTEDVALUE(Table[Scanner type]) 
return
CALCULATE([Measure];Table[Scanner type] = scanner_selected)

Adjust as necessary.

Regarding the second part you can have one of two options or make that when values of measure is 0 return blanks (but this will impact previous values) or filtering on future dates to force blanks. Can you share the formula for the flateen lines please.

[ChristianRHouen]

@MFelix 

Sorry for bothering you again - i know you're probably very busy. Nevertheless, do you think you have an answer for above questions?