accmulative line graph

[colourfullife]

Hi All,

I need your help.

I created accumul. line graph with query below.

it's about progress ( plan vs actual) graph by week.

CSU SP_Contractual =

CALCULATE(

    COUNTA('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE]), USERELATIONSHIP('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE],'calendar'[Date]),

    FILTER(

        ALLSELECTED('Calendar'[Date]),

        ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)))

i have one issue. the line goes till week where doesn't have any value..

is there any way my line stop till there is value?

thank you,

CL

[v-zhenbw-msft]

Hi @colourfullife ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft]

Hi @colourfullife ,

Don't you want to show the straight line on the right?

If yes, maybe you can change your FILTER formula.

We create a sample the you can refer.

Maybe you can try the following measure.

CSU SP_Contractual =
CALCULATE (
    COUNTA ( 'Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE] ),
    USERELATIONSHIP ( 'Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE], 'calendar'[Date] ),
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ),
        ISONORAFTER (
                'Calendar'[Date], MAX ( 'Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE] ), DESC
        )
    )
)

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[colourfullife]

Hi @v-zhenbw-msft 

Thank you for your reply.

Yes what i want is i don't want to see the line right.

There is no value but it still contines.

I tried the query you suggested.

It doens't show anymore the line right but the value is incorrect.

  - The delivery doesn't start from week 28, it starts from W33.. and the value is also not correct.

sharing the example database excel file : Example database 

thank you,

CL

[v-zhenbw-msft]

Hi @colourfullife ,

We can create two measures and there is no relationship between two tables to meet your requirement.

Measure = 
CALCULATE(
COUNTA('Table'[Delivery]),FILTER('Table','Table'[Delivery]=MAX('Date'[Date])),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Table'[Delivery])))

Measure 2 = 
CALCULATE(
    COUNTA('Table'[IRN]), 
    FILTER('Table','Table'[IRN]=MAX('Date'[Date])),
    FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Table'[IRN])))

We found there is blank value in your IRN column.

Maybe you can separate the two columns and delete the blank value in IRN column using Power Query Editor.

Then create two active relationships using the two tables and Date table.

At last we can create two measures.

Measure 3 = 
CALCULATE(
    COUNTA('Table (3)'[Delivery]),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Table (3)'[Delivery])))

Measure 4 = 
CALCULATE(
    COUNTA('Table (2)'[IRN]),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Table (2)'[IRN])))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[colourfullife]

Hi @v-zhenbw-msft  

This is my query for accumulative line by week.

I have a relationship between my data and date using usereltionship..

CSU SP_Contractual try1 =

CALCULATE (

    COUNTA ( 'Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE] ),

    USERELATIONSHIP ( 'Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE], 'calendar'[Date] ),

    FILTER (

        ALLSELECTED ( 'Calendar'[Date] ),

        ISONORAFTER (

                'Calendar'[Date], MAX ( 'Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE] ), DESC

        )

    )

)

[v-zhenbw-msft · Accepted solution]

Hi @colourfullife ,

There is an inactive relationship, so the date will show the date of the active relationship.

So we need to create two tables and create relationship separately.

1. In power query editor, we need to copy a table and delete the Delivery column. Then filter out the null value in the IRN column.

2. Then create active relationship between date table.

3. At last we can create two measures to get the result.

Measure = 
CALCULATE (
    COUNTA ( 'Data - CSU SP PO'[Delivery] ),
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ),
        ISONORAFTER (
                'Calendar'[Date], MAX ( 'Data - CSU SP PO'[Delivery] ), DESC
        )
    )
)

Measure 2 = 
CALCULATE (
    COUNTA ( 'Data - CSU SP PO (2)'[IRN] ),
   FILTER (
        ALLSELECTED('Calendar'[Date]) ,
        ISONORAFTER (
                'Calendar'[Date], MAX ( 'Data - CSU SP PO (2)'[IRN] ), DESC
        )
    )
)

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[amitchandak]

@colourfullife , You try using the date from table in max

CSU SP_Contractual =
CALCULATE(
COUNTA('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE]), USERELATIONSHIP('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE],'calendar'[Date]),
FILTER(
ALLSELECTED('Calendar'[Date]),
'Calendar'[Date]<= MAX('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE])))

[parry2k]

@colourfullife you can do something like this

CSU SP_Contractual =
VAR __c =  COUNTA('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE])
RETURN
CALCULATE(
   COUNTA('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE]), USERELATIONSHIP('Data - CSU SP PO'[CONTRACTUAL_DELIVERY_DATE],'calendar'[Date]),
    FILTER(
        ALLSELECTED('Calendar'[Date]),
        ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)))
*
DIVIDE ( __c, __c )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[colourfullife]

@parry2kthanks for your reply.

in fact, i have a few more lines to add in the same graph.

when i use the same query you advised for others, the other line doen't look right in the graph..?

CSU SP_IRN 1 =

VAR __C =  COUNTA('Data - CSU SP PO'[IRN_Graph])

RETURN

CALCULATE(

   COUNTA('Data - CSU SP PO'[IRN_Graph]), USERELATIONSHIP('Data - CSU SP PO'[IRN_Graph],'calendar'[Date]),

    FILTER(

        ALLSELECTED('Calendar'[Date]),

        ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)))

*

DIVIDE ( __C, __C )

any ideas why?