Cumulative Sum less then Current Date

[Suhel_Ansari]

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

[v-yadongf-msft]

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]

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 · Accepted solution]

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]

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

[v-yadongf-msft]

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]

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

[Suhel_Ansari]

@amitchandak  , @Greg_Deckler , @parry2k  , please asssit. Thanks