Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Icon for Helper V rankHelper V
3 years ago
Solved

Cumulative Sum less then Current Date

Hi All,

I need the Cumulative Sum measure for the column based on the less then Current Date which means I want to add the colum "ST_Day_Duration" provided the Contract End Date is less then Today() as seen in the foolowing screen print. I want to Sum the ST_Day_Duration column for India except the contract Date less then today.

Similarly I want to sum the USA except the contract Date less then today.

Please assist with the Mesure or column.

Thanks

Suhel

 

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi Suhel_Ansari 

     

    Create a measure:

    Measure = 
IF (
    MAX('Table'[Contract end date]) <= TODAY (),
    CALCULATE (
        SUM ( 'Table'[ST_Day_Duration] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Contract end date] <= TODAY ()
                && 'Table'[Country] = SELECTEDVALUE( 'Table'[Country] )
        )
    )
)

     

    Best regards,

    Yadong Fang

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

7 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Suhel_Ansari ,

     

    In order to better understanding your demands and give the right solution, could you please tell me what's your expected output?

     

    Thanks for your efforts & time in advance.

     

    Best regards,

    Yadong Fang

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

    • Suhel_Ansari's avatar
      Suhel_Ansari
      Icon for Helper V rankHelper V
      3 years ago

      Hi v-yadongf-msft ,
      I want the Cumulative Sum for all the row for the "ST_Day_Duration" for a given Country provided the Contract End Date is less then Today() Date. Exmaple for the country INDIA i want to sum the (16.9389+7.0278+20.916) = 44.8827 Rows, Except the first Row whos Contract End Date is "5/18/2026".

      Regards

      Suhel

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Suhel_Ansari ，

     

    Please try:

    Column = 
IF (
    'Table'[Contract end date] <= TODAY (),
    CALCULATE (
        SUM ( 'Table'[ST_Day_Duration] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Contract end date] <= TODAY () )
    )
)

     

    Best regards,

    Yadong Fang

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

    • Suhel_Ansari's avatar
      Suhel_Ansari
      Icon for Helper V rankHelper V
      3 years ago

      hi v-yadongf-msft ,

      It a partial answer I am getting wrong answer if i have more Country as seen in the attached screen print and moreover I need a Measure please assit. Thanks

       Regards

      Suhel

      • v-yadongf-msft's avatar
        v-yadongf-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Suhel_Ansari 

         

        Create a measure:

        Measure = 
IF (
    MAX('Table'[Contract end date]) <= TODAY (),
    CALCULATE (
        SUM ( 'Table'[ST_Day_Duration] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Contract end date] <= TODAY ()
                && 'Table'[Country] = SELECTEDVALUE( 'Table'[Country] )
        )
    )
)

         

        Best regards,

        Yadong Fang

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