running total showing wrong Total

[LP280388]

Hi Team,

I have a measure as below to calculate the running total of IDs. however I see a strange wrong total issue as highlighted below. Please help me what resolve this issue. 

For 04/09/2022 to 04/12/2022 it should show 17220 as Running total instead of 17219.

Measure : 

Running Total=

var maxdate = Max('data'[DATE])

return CALCULATE(COUNT('data'[ID]),ALL('data'[DATE]),'data'[status]="Completed",'data'[DATE] <= maxdate) 

[AmiraBedh]

Can you try the following : 

Running Total =
VAR maxdate = MAX('data'[DATE])
RETURN
    CALCULATE(
        COUNT('data'[ID]),
        FILTER(
            ALL('data'),
            'data'[DATE] <= maxdate && 'data'[status] = "Completed"
        )
    )

I used FILTER(ALL('data'), ...) instead of ALL('data'[DATE])to ensure that all filters are removed from the entire data table but keeps the logic that checks the date and status.

[LP280388]

Hi Thanks for your reply. I tried but no change in the result.  I did try Quick Measure > Running Total.   This also gives the same result.

[AmiraBedh]

Can you share your pbix file ?

[LP280388]

Sorry I may not be able to share the file at this point. However, I would like to mention, there is a filter on the page level from another table.  that filter is filtering out Invalid employees.  

do you think that has any effect on this issue?

[v-tianyich-msft · Accepted solution]

Hi @LP280388 ,

I did simple samples as it might be a bit difficult to write more than 10,000 data so I used sum instead of count and you can check the result as below:

Running Total = 
var _t = ADDCOLUMNS('Table',"Total",SUMX(FILTER(ALL('Table'),[DATE]<=EARLIER([DATE])&&[status]="Completed"),'Table'[ID]))
return 
SUMX(_t,[Total])

You can simply change it to suit your needs.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Greg_Deckler]

@LP280388 It's a single table data model, CALCULATE is not going to work. You need to use the No CALCULATE approach below. I made a video on this exact topic:

[AmiraBedh]

Maybe. You can still send me by message the file.