Stop Cumulative count curve

[amirghaderi]

Hi,

I have seen a lot post on this subject. but, could not get a clear answer.

I have a measure which count a column record, there is a cumulative count measure which I want to stop once it reaches its Maximum and not to continue as a flat line.

Data table as below:

Criteria	Date
MDR Issue Date	Friday, 19 June 2020
MDR Planned Finish Date	Monday, 8 June 2020
MDR Issue Date	Friday, 26 June 2020
MDR Planned Finish Date	Monday, 8 June 2020
MDR Issue Date	Friday, 5 June 2020
MDR Planned Finish Date	Monday, 8 June 2020
MDR Issue Date	Friday, 26 June 2020
MDR Planned Finish Date	Thursday, 11 June 2020
MDR Issue Date	Monday, 15 June 2020
MDR Planned Finish Date	Tuesday, 30 June 2020
MDR Issue Date	Friday, 3 July 2020
MDR Planned Finish Date	Thursday, 11 June 2020
MDR Issue Date	Monday, 15 June 2020

Issued Count =

    calculate(COUNT('t_MDR2'[Date]),t_MDR2[Criteria] = "MDR Issue Date")

Issued **bleep**. =

CALCULATE(

    [Issued Count],

FILTER(

        ALLSELECTED('t_MDR2'),

        't_MDR2'[Date]<= MAX(t_MDR2[Date])))

[amirghaderi]

If you look at the graph, the green curve has been extended as a flat line once it has reached its maximum value. i want that to finish once it reaches its max value

[danextian]

Hi @amirghaderi ,

Are you using a separate a date field from a separate Date table in  your graph? You can modify your formula  a bit so the value only shows when ony when the date from a separate table is <= the date from your fact table. Try this.

Issued **bleep**. =
IF (
    LASTDATE ( Dates[Date] )
        <= CALCULATE ( MAX ( 't_MDR2'[Date] ), ALL ( 't_MDR2' ) ),
    CALCULATE (
        [Issued Count],
        FILTER ( ALLSELECTED ( 't_MDR2' ), 't_MDR2'[Date] <= MAX ( t_MDR2[Date] ) )
    )
)

[amirghaderi]

Hi, Yes I have a table for dates.

I changed the measure as per your note and no chnage to the output.

[danextian]

Hi @amirghaderi ,

I have attached  sample pbix with a formula similar to what i previously posted.

[amitchandak]

@amirghaderi ,The information you have provided is not making the problem clear to me. Can you please explain with...

[amirghaderi]

Hi, thanks for the response. getting closer now. 

I dont have the option to attached the file.

I think I cant get the same result since my count data is measure.

So, assume in your file the value table has three columns, as below. I want to count the cumulative value of "Issued" and "planned" as two curve over the date

Document	Criteria	Date
A	Issued	Friday, 19 June 2020
B	Planned	Monday, 8 June 2020
C	Issued	Friday, 26 June 2020
D	Issued	Monday, 8 June 2020
E	Planned	Friday, 5 June 2020
F	Issued	Monday, 8 June 2020
G	Issued	Friday, 26 June 2020
H	Issued	Thursday, 11 June 2020
I	Planned	Monday, 15 June 2020
J	Planned	Tuesday, 30 June 2020
K	Planned	Friday, 3 July 2020
L	Planned	Thursday, 11 June 2020
M	Issued	Monday, 15 June 2020

[v-yingjl]

Hi @amirghaderi ,

Try like this measure:

Issued Count = 
IF (
    SELECTEDVALUE ( 'Table'[Date] )
        <= CALCULATE (
            MAX ( 'Table'[Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Criteria] = "Issued" )
        ),
    CALCULATE (
        COUNT ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Criteria] = "Issued"
        )
    )
)

Attached my sample file that hopes to help you: Stop Cumulative count curve.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amirghaderi]

Hi,

Still the same issue as below:

[v-yingjl]

Hi @amirghaderi ,

Could you please share a sample file for further discussion? Please remember to replace the sensitive information when sharing sample file.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amirghaderi]

Hi,

How can I attached PBI file? I cant find any attachment option.

[v-yingjl]

Hi @amirghaderi ,

You can upload the sample file to a place where you can store files like Onedrive for Business etc. and share the file link.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.