Remove repetitive 0% and 100% out of graph

[Franki9]

Hi,

The graph I am building is a timeline of progress for construction, I have combined weekly reports over years. So in short the data looks as follow:

Date	Progress	Areas
10 03 2017	0%	AREA 1
17 03 2017	0%	AREA 1
25 03 2017	0%	AREA 1
12 04 2017	50%	AREA 1
20 04 2017	100%	AREA 1
25 04 2017	100%	AREA 1
10 05 2017	100%	AREA 1

Im struggling to remove all the unesarry 0% and 100% from my graph, there should 1 off each and the rest should be removed from the calculation. See my calculation I have made the doesnt seem to work, please help.

Erected Final =

var Finish_date = CALCULATE(MIN('CALENDAR'[Date]),FILTER('Unit 2 - 6 - 21 Mar 2017+', 'Unit 2 - 6 - 21 Mar 2017+'[ERECT / LEVEL & SECURE] = 1.00))

var Start_Date = CALCULATE(MAX('CALENDAR'[Date]), FILTER('Unit 2 - 6 - 21 Mar 2017+', 'Unit 2 - 6 - 21 Mar 2017+'[ERECT / LEVEL & SECURE] = 0.00))

return

CALCULATE(SUM('Unit 2 - 6 - 21 Mar 2017+'[ERECT / LEVEL & SECURE]),

FILTER('CALENDAR','CALENDAR'[Date] >= Start_Date

&& 'CALENDAR'[Date] <= Finish_date)

)

Kind Regards,
Frank 

[Anonymous]

Hi Frank,

Please try this measure.

Erected Final = 
VAR _Finish_date =
    CALCULATE (
        MIN ( 'Unit 2 - 6 - 21 Mar 2017+'[Date] ),
        FILTER (
            ALL('Unit 2 - 6 - 21 Mar 2017+'),
            'Unit 2 - 6 - 21 Mar 2017+'[ERECT / LEVEL & SECURE] = 1.00
        )
    )
VAR _Start_Date =
    CALCULATE (
        MAX( 'Unit 2 - 6 - 21 Mar 2017+'[Date] ),
        FILTER (
            ALL('Unit 2 - 6 - 21 Mar 2017+'),
            'Unit 2 - 6 - 21 Mar 2017+'[ERECT / LEVEL & SECURE] = 0.00
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Unit 2 - 6 - 21 Mar 2017+'[ERECT / LEVEL & SECURE] ),
        FILTER (
            'CALENDAR',
            'CALENDAR'[Date] >= _Start_Date
                && 'CALENDAR'[Date] <= _Finish_date
        )
    )

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Franki9]

Hi Gao,

Thanks so much for coming back to me, the calculation isnt working as I think it is due to the additional data and filters added the calculation should look at the columns filtered. See below the sample data and my current results, thanks again for your help.

Sample Data

Date	Area	Area Number	Boiler	Mass/qty	Mass/Qty Erected	Erected %	Type
16 Jan 2017	CAC2	Structural	Boiler 2	16849.7	0	0	Structural
16 Jan 2017	CAC3	Structural	Boiler 3	16849.7	0	0	Structural
16 Jan 2017	CAC4	Structural	Boiler 4	16845.2	0	0	Structural
16 Jan 2017	CAC5	Structural	Boiler 5	16845.2	0	0	Structural
16 Jan 2017	CAC6	Structural	Boiler 6	16845.2	0	0	Structural
16 Jan 2017	SILO2	Structural	Boiler 4	126723.7	0	0	Structural
16 Jan 2017	SILO2	Structural	Boiler 4	815.8	0	0	Structural
16 Jan 2017	T2D	Structural	Boiler 4	20134.5	0	0	Structural
16 Jan 2017	T2E	Structural	Boiler 4	20134.5	0	0	Structural
16 Jan 2017	T2F	Structural	Boiler 4	20134.5	0	0	Structural
16 Jan 2017	T3D	Structural	Boiler 4	59983.1	0	0	Structural
16 Jan 2017	T3D	Take-Up & Drive Sheeting Structure	Boiler 4	18175.5	0	0	Structural
16 Jan 2017	T3E	Drive Area Sheeting Structure	Boiler 4	12064.7	0	0	Structural
16 Jan 2017	T3E	Structural	Boiler 4	40654	0	0	Structural
16 Jan 2017	T3F	Structural	Boiler 4	40305.7	0	0	Structural
16 Jan 2017	T4A	Structural	Boiler 2	47842.9	14690.3	0.307053	Structural
16 Jan 2017	T4A	Structural	Boiler 3	47203.8	0	0	Structural
16 Jan 2017	T4B	Structural	Boiler 2	23933.8	7149.2	0.298707	Structural
16 Jan 2017	T4B	Structural	Boiler 3	23636.9	0	0	Structural
16 Jan 2017	T4C	Structural	Boiler 2	21039.6	7075.3	0.336285	Structural
16 Jan 2017	T4C	Structural	Boiler 3	20741.2	0	0	Structural

As you can see below I have multiple filters as from table above.

Below is the calculation I took from you and abused it probably, but I believe the problem is with the calculation filter that Im missing. 

Erected 2 = 
VAR _Finish_date =
    CALCULATE (
        MIN ( 'STR-PLT-MEC'[Date] ),
        FILTER (
            'STR-PLT-MEC',
            'STR-PLT-MEC'[Mass/Qty Erected] = 'STR-PLT-MEC'[Mass/qty]
        )
    )
VAR _Start_Date =
    CALCULATE (
        MAX( 'STR-PLT-MEC'[Date] ),
        FILTER (
            'STR-PLT-MEC',
            'STR-PLT-MEC'[Mass/Qty Erected] = 0.00
        )
    )

RETURN
        CALCULATE(
            DIVIDE(CALCULATE(SUM('STR-PLT-MEC'[Mass/Qty Erected])),           
            CALCULATE( SUM('STR-PLT-MEC'[Mass/qty]))),
        FILTER(
                'CALENDAR',
                'CALENDAR'[Date] >= _Start_Date
                    && 'CALENDAR'[Date]<= _Finish_date
        )
        )

Kind Regards,
Frank