Search code examples
excelpowerbidaxpowerpivot

How to display blanks after 100% is reached? DAX measure

I have created a measure which calculates the percent of completion for each project. But my client wants it to display blanks after the project is completed, i.e. after the first 100%.

I already tried using an If function, but it is returning the same value for every month. I also looked online, but did not find a solution. Here is my %OfCompletion measure and the measure that it depends on.

% of Completion:= 
VAR sproject =
    IF ( HASONEVALUE ( Project[Project] ), VALUES ( Project[Project] ) )
RETURN
    CALCULATE (
        DIVIDE (
            [S Expenses Running Total],
            CALCULATE (
                [Total Sales Costs] + [Total Sales Hours],
                ALL ( Sales ),
                Sales[Project] = sproject
            )
        ),
        Project[Classification] = "IN"
    )


Expenses Running Total:= CALCULATE (
    [Total Sales Costs] + [Total Sales Hours],
    FILTER (
        ALL ( Dates ),
        Dates[Current Month Offset] <= MAX ( Dates[Current Month Offset] )
    )
)

Example Values, Example Values, And a screenshot of my model. And a screenshot of my model.

Solution

  • This checks if current month completion is 100% AND prior month completion is also 100%, and returns BLANK - otherwise returns the actual completion value:

    Monthly Completion Measure:=
IF ( 
    [% of Completion] = 1 && 
    CALCULATE ( 
        [% of Completion],
        PARALLELPERIOD ( 
            Dates[Date],
            -1,
            MONTH
        )
    ) = 1,
    BLANK,
    [% of Completion]
)