Cumulative total in dax

[Mgau63100]

Hi All

Could you please help me in achieving below results in dax :

Table:1

Eventdate                Number      Output 

01/08/2020 01:00.          0.             0

01/08/2020 01:30.           1.             1

01/08/2020 01:38.            1.             2

01/08/2020 01:58.            1.             3

01/08/2020 03:00.            0.             0

01/08/2020 04:00.             1.             1

01/08/2020 05:00.             0.             0

If we have 1 then add else 0. It should work on filter also like if I want to filter between 3 pm to 5pm then it should consider only data between intervals .

We need output in dax as measure 

[Fowmy]

@Mgau63100 

This measure should work for you:

Measure = 
VAR V2 = CALCULATE( MAX('Table'[Date]), FILTER( ALL('Table'), 'Table'[Date] < MAX('Table'[Date]) && 'Table'[Number]=0))
VAR V1 = 
IF( MAX( 'Table'[Number] )  = 0 , 0,
CALCULATE(
    SUM('Table'[Number]),
    FILTER(
        ALL('Table'),
        'Table'[Date] <= MAX('Table'[Date]) && 'Table'[Date] > V2  )
    )
)
RETURN
V1

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Mgau63100]

Thank you for sharing the measure formula. It is working fine but when i am doing any filters it should adjust accordingly which is not happening. e.g IF i want to filter between 2 dates or time stamps then formula should calculate from 1st element not before that. Is there anyway to do that ?