Running Total without gaps

[a_cutler]

Hi, 

I really hope someone can help. I've been using Dax for a while but I cannot figure out how to make ths work.

I have a running total where I'm getting no data inbetween data points like below;

So it looks like this;

     Jan     Feb     Marc   etc

1     0       1           1  

2     1       0           2

3     0       0           0

4     2       0           0

5     3       2           3

I need it to look like this;

     Jan     Feb     Marc   etc

1     0       1           1  

2     1       1           2

3     1       1           2

4     2       1           2

5     3       2           3

The dax being used is;

Breach Count running total Contract Col =

CALCULATE(

    sum('Breach Month'[Breach Count Col]),

    FILTER(

        ALL('Breach Month'[Breach Month of Contract Col]),'Breach Month'[Breach Month of Contract Col] <=MAX('Breach Month'[Breach Month of Contract Col])

    )

)

For the months across the top of the visual a column is being used in the same table as the rest of the data.

I've tried all the different ways of doing it that i can find in the community but they all do the same thing.

Thanks for your help!!

Al

[AlB]

Hi @a_cutler 

You need to show the tables in your data model involved in the calculations and explain how you are building the visual (what fields go where, etc.). Otherwise we can just guess.

Show your data in text-tabular format in addition to (or instead of) the screen captures. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

[a_cutler]

Hi,

Thanks, I'm getting an error about the number of characters when I paste the table in - what's the best way to share it?

I've limited the table to 200 rows as that's what's needed to illustrate the issue.

Thanks,

Alan

[AlB]

You can share the URL to the file hosted elsewhere (Dropbox, OneDrive... or sites like tinyupload.com, where no sign-in is required)

[a_cutler]

@AlB  Is the above file ok?

Thanks,

Al

[a_cutler]

@AlB  - Apologies, for a little more context of what I'm trying to, I'm looking to produce a table that has the running total of column RunningAgeTotal without the blanks. I'd like to split the table so it has DatepaidoutShort across the top and Breach Month of Contract Col as the rows. 
I've included a picture of the table I'm trying to produce in the file below.

https://ufile.io/onulz

The calculation I'm trying to use below is suming up the runningagetotal column where days to clear is over 30 or is blank and also where breach count col is 1.

Breach Month of Contract Col = CALCULATE(sum('Breach Month'[RunningAgeTotal]),or('Breach Month'[DaysToClear]>30,ISBLANK('Breach Month'[DaysToClear]) = TRUE()),'Breach Month'[Breach Count Col] = 1)

I need to be able to use slicers on the data once it's produced the table.

Please do let me know if you need to know anything else.

Thanks,

Alan

[a_cutler]

OK, here's a file with example data.

https://ufile.io/6j0qr

Thanks