Cumulative Sum measure showing correct figures only when filtered in slicer

[IvanS]

Dear all,

I am creating Cumulative sum for time spent  (WORKLOG_LOGGED_TIME) within table Worklogs. I have managed to create the measure that is showing the correct figures only when slicer is applied. When I clear the slicer, the figures are incorrect - I do believe that it is related to ALLSELECTED function however I tried to remove it or replace it with ALL but still without any success.

CUMULATIVE_LOGGED_TIME =

CALCULATE(SUM(Worklogs[WORKLOG_LOGGED_TIME]),

FILTER(

ALLSELECTED('Worklogs'),

'Worklogs'[WORKLOG_CREATION_DATE] <= MAX (Worklogs[WORKLOG_CREATION_DATE])

))

Slicer is applied:

Slicer is NOT applied:

I do also have date table as best practise, but if using data table, the measure is only summing the time spent on monthly basis - see below:

Any help is much appreciated!

[amitchandak]

@IvanS , Will work best with date table

CUMULATIVE_LOGGED_TIME =
CALCULATE(SUM(Worklogs[WORKLOG_LOGGED_TIME]),
FILTER(
ALLSELECTED('Date'),
'Date'[Date] <= MAX (Date[Date])
))

or

CUMULATIVE_LOGGED_TIME =
CALCULATE(SUM(Worklogs[WORKLOG_LOGGED_TIME]),
FILTER(
ALLSELECTED('Worklogs'),
'Worklogs'[WORKLOG_CREATION_DATE] <= MAX (Worklogs[WORKLOG_CREATION_DATE])

&& 'Worklogs'[Issue_key] <= MAX (Worklogs[Issue_key])
))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[IvanS]

Hi @amitchandak 

starting from the bottom with solution without date table - working when filterred, not working when filter is cleared (expecting value of 19 800)

Solution with date table - only summing the time spent per each month (not cumullating).

Below is the 1-to-many relationship between the tables. The date table was configured as per this link: Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

Any ideas what might be wrong?

[v-easonf-msft]

Hi, @IvanS 

The specific reason is difficult to determine.

Can you post some sample data in table form or pbix file(without sensitive data) and your expected result for further research?

Best Regards,
Community Support Team _ Eason