Sum with condition

[colourfullife]

Hi Guys,

I have a question for you.

I want to sum a column which has number figure if in the other coloums has a date value.

for instance, i have a table like below.

I want to show accumulative line for start and finish. 

Line	Start	Finish
14	2020/11/09
23	2020/11/10	2020/12/05
34	2020/11/11	2020/12/05
5
6	2020/11/09	2020/12/05
72	2020/12/05
5
2	2020/12/01	2020/12/05
35	2020/12/03	2020/12/05

I tried the code below to get what i want but this one sums up all Line column figures.

Actul IRN date =

VAR _End = CALCULATE ( MAX ( 'SPARES - PO STATUS'[ACTUAL IRN DATE] ), ALL ( 'calendar' ),USERELATIONSHIP('SPARES - PO STATUS'[ACTUAL IRN DATE],'Calendar'[Date]))

RETURN

    IF (

        _End < MIN ( 'calendar'[Date] )|| isblank(MAX ( 'SPARES - PO STATUS'[CDD] )),

        BLANK (),

CALCULATE(

    Sum('SPARES - PO STATUS'[PO_LINE_QUANTITY]), USERELATIONSHIP('SPARES - PO STATUS'[ACTUAL IRN DATE],'calendar'[Date]),

    FILTER(

        ALLSELECTED('calendar'[Date]),

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

any advices would be appreciated.

Regards,

CL

[v-zhenbw-msft]

Hi @colourfullife ,

Do you mean that you have four columns in one table?

What is the structure of your table? Could you please provide a mockup sample based on fake data?

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.

[v-zhenbw-msft]

Hi @colourfullife ,

Do you want to calculate the accumulative line like this following screenshots?

If yes, we can create two measures to meet your requirement.

Measure = CALCULATE(SUM('Table'[Line]),FILTER('Table','Table'[Start]=MAX('Date'[Date])))+0

Measure 2 = 
SUMX(FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])),[Measure])

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 

Yes, that's what i want to create.

I have 4 date columns to show in a line graph.

But all lines start and end in same dates which is not corret.

do you know how make line start as the date value start and ends?

Regards,

CL

Regards,

CL 

[amitchandak]

@colourfullife , refer to if my Blog on HR can help in this case

 https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[AllisonKennedy]

@colourfullife Sorry, I don't understand what your sample data relates to in the sample formula - there is no reference to [Line], [Start] or [End] columns? Can you provide a sample output of what you want and describe why the formula you have tried/posted does not work?