Running Total with Blanks

[marcopaz]

Hello,

I've been trying to do a column to show running totals for two Contracts, and the problem I am facing is that sometimes different Contracts have different PeriodDate (date that the contract was paid, which is the PaidThisEstimate), which is causing these entries to be blank (instead of repeating the Cumulative). See the picture below (I wrote with red font the expected results):

 I tried a lot of different solutions I've seen in the forum, but none of them seems to be working, see below my Measure:

Cumulative Earnings Test2 = 
CALCULATE(
    SUM('Historical Earnings'[PaidThis Estimate]),
    FILTER(
        ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

Could you please help me ?

Thanks 

[Anonymous]

One has to see your model to be able to tell you what's wrong. It looks like in your table you are using a different column of dates (Contract ID Date) than the calculation itself ('Calendar'[Date]). This is most likely where the problem lies. You should always be careful which columns you're dropping on the canvas and which you're using in your measures.

[marcopaz]

You are correct, I got a fact table with "PeriodDate", which has only the specific day in each month the contracts were paid, and then I have a calendar date table, see below:

My main goal is to be able to create a Scatter Chart which shows Contracts progression with regards to %Paid and %Duration used. I did the scatter chart and it worked well, but when I put the Play Axis, if a contract hasn't been paid in that specific month, it would dissapear from the scatter chart, see below:

As you can see above the there were a total of 7 Contracts in June 2021, however in July of 2021 only three contracts billed us, so then all the other contracts "dissappear" see below:

I would like the contract to still be shown in the scatter chart for months that they didn't bill us, that is why I am trying to get the Running Total fixed.

[Anonymous · Accepted solution]

Mark your Date table as a date table in the model, then use this table to mean time in your visuals (by the way, you should never put any columns from your fact table on the canvas) and the measure you've shown above should work correctly. A good dimensional design (on which PBI has been founded) means  that you should have conformed dimensions and fact tables that are linked through dimensions. Also, your fact tables should be hidden from the end user. They should not be allowed to select columns from fact tables. A good fact table only stores keys to dimensions and facts (measurements). All measurements are exposed through measures and slicing is only allowed via dimensions.

[marcopaz]

You are right, when I did this data model I didn't have much experience with Power BI/Databases, but I am planning to do a good clean up on my tables to reduce the redundancy I got my files. I found a where the problem was, which was the fact that I had the calendar, the dimension and the fact tables all connected through "Both ways" relationship, which was messing it up due to the Crossfilters. But anyway, it looks like I need to put some work on optmizing the data model, but thanks for the solution.

[amitchandak]

@marcopaz , Try with this modification

Cumulative Earnings Test2 = 
CALCULATE(
    SUM('Historical Earnings'[PaidThis Estimate]),
    FILTER(
        ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

[marcopaz]

I tried that and the same problem.