Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Sum with condition

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. 

 

LineStart

Finish

142020/11/09 
232020/11/102020/12/05
342020/11/112020/12/05
5  
62020/11/092020/12/05
722020/12/05 
5  
22020/12/012020/12/05
352020/12/032020/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
Need Help
Show and Tell

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    Anonymous 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?

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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 

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    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.