Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper 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()...
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.

v-yadongf-msft's avatar
v-yadongf-msft
Community 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
    Helper 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
      Community 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.

      • Suhel_Ansari's avatar
        Suhel_Ansari
        Helper V
        3 years ago

        Hi v-yadongf-msft  , 
        Thank you for the prompt respone, I need to cretae a another Measure to get the "Max - ST_Day_Duration" for the Future date for Example as seen in the following screen print, for India the future date is "5/18/2026" and the value should be "210.3903" similarly for USA the date is "10/6/2025" and the value should be "69.3903", please assist. Thanks🙏

         

        Regards

        Suhel