Cumulative Total with data gaps

[kyrpav]

I want to have a cumulative total for some tasks and hours where gap in the data exits. At this point visualization where data do not exists in pivot table is empty but it should have been shown the previous value cause we always sum(hours) for date<=selected_date

I am giving you sampe data and my code and visulization, is there any solution to this?

(i do not know why table view does not show all data in its row properly  and line seperators are above data.[this is another type of issue ] but i suppose it is easy to check.)

Code:

**bleep**-Contracted Hours =

var untilDate=SELECTEDVALUE('Table'[Date])
var selTask=SELECTEDVALUE('Table'[Task])

Return
     CALCULATE(
        SUM('Table'[Hours]),
        FILTER(
           ALL('Table'),
           'Table'[Task]=selTask &&
           'Table'[Date]<=untilDate))

Result on visual:

As you see on first 4 months where all tasks have data it works properly whne then for May and June only one task has data then data are blank. 

I saw another post :

https://community.powerbi.com/t5/Desktop/Tracking-Sales-Returns-over-Time/m-p/1404161#M595155

but i could not exactly understand what they are proposing.

I am giving you a link to the pbix example that i did. I have made 3 tables for the first table which is  my problem here the calculation is not working for the next 2 tables that you can see in example the calculation is working. I can not see the exact reason. Especially table 1 and table 3 i think they do the same thing.

https://drive.google.com/file/d/1KaQJHGnBhRb562Bw0WV2CXRqh0vmd8uN/view?usp=sharing

[v-kkf-msft]

Hi @kyrpav ,

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[amitchandak]

@kyrpav , better to create a measure like, if needed use data table

CALCULATE(
SUM('Table'[Hours]),
FILTER(
ALL('Table'),
'Table'[Task]=max('Table'[Task]) &&
'Table'[Date]<=max('Table'[Date])))

or

CALCULATE(
SUM('Table'[Hours]),
FILTER(
ALLselected('Table'),
'Table'[Task]=max('Table'[Task]) &&
'Table'[Date]<=max('Table'[Date])))

with date table

CALCULATE(
SUM('Table'[Hours]),
FILTER(
ALLselected('Date'),
'Date'[Date]<=max('Date'[Date])))

[kyrpav]

I have tried to recreate another table and it works properly but my main table is not. I am watching the code and it is the same i do not see the issue. I have placed a pbix in the main post maybe someone can check it.

There is no logic  that i can see to explain why it does not work.

[v-kkf-msft]

Hi @kyrpav ,

Based on my understanding, this is because your table does not contain May and June dates, and the measure is based on context, so it will not calculate dates that do not exist. You can convert the matrix visual into a table, and it shows this more clearly.

Therefore, you need to add another date table, and set the date as the column of the matrix, and then modify the measure.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[kyrpav]

Since the measure filters all Dates <= MaxDate and the max date is selected cause it exists in the pivot how is it possible that the previous dates are not in the context and with them de data for the relative tasks.?

[v-kkf-msft]

Hi @kyrpav ,

The reason why the MeasureT3A returns the correct value is the months in your Table(3) are consecutive. So the MeasureT3A is not empty in these months. In other words, when a month does not exist in your table, there is no so-called context, and the measures will not be calculated. So you need to have consecutive months in your table, or associate other tables that has consecutive months.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[kyrpav]

I saw this part but calculation is <= that date so it should have found the rest of the records.

Also in the .pbix there is table 3 as example that works and i do not see the diff.

Again gaps on data exist cause in db it should not be empty records. but the context of the calculation is ok. My new question here is why in second case the filter is qorking and in first not.

[kyrpav]

@amitchandak None of these 3 solutions solved the issue. In this case senario the measure is created inside the table and it does not work but from the main report these cumulative measures are placed in seperate table and also i try to use the selected value in order to get the data from the proper selected tasks from visual

[v-kkf-msft]

Hi @kyrpav ,

Create a calendar table like this:

Date = CALENDAR(DATE(2021,1,1), DATE(2021,8,31))

Then try the following formula:

Measure = 
    CALCULATE(
        SUM('Table'[Hours]),
        ALL('Date'),
        filter(ALL('Table'[Date]),'Table'[Date] <= max('Date'[Date]) )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[kyrpav]

The issue from what i have understood is not about the date only but about the task selection also. For some reason this table does not filter properly on pivot table.

I have tried to do a cumulative count and change to static selection either date , either task, either both and function was warking when everthing where static i am giving you the screenshots. I placement of cumulative-contracted Hours the measure is also changes to show max task(i am also giving you the code below.

Also we are talking about a report that connects to views of a db so calendar solution for every table is not ok in general for me. This test report that i am giving is created in order not to share confidential data.

On more thing if you download my pbix you will see that the same functions for table 3 are working and the visual is ok and i can not find the difference

**bleep**-Contracted Hours = 
var x =MAX('Table'[Task])
return
x
/*
        CALCULATE(
            SUM('Table'[Hours]),
            filter(ALL('Table'),'Table'[Date]<=date(2021,06,01) && 'Table'[Task]=x))*/