Issue with Filtered running total

[Anonymous]

Hi Community!

I've been breaking my head for the past 3 days, googling and reading through this forum. I can't seem to find the solution to my problem, and would like to ask for your much valued help!

I have a Calendar Table for the whole year as follows:

I then have a YTD file with all completion orders from an Assembly site. I have a connection from the DATE column on that file, to the DATE column on the CALENDAR table shown above.

I created a calculation that would tell me the DELTA (Goal - Actual) for each day of the filtered week:

DELTA =
SUM('Campus'[Hours]) - SUM('Campus Goals'[GOAL HRS])

But the issue comes with the Running Total of this Delta:

RT =
CALCULATE(
[DELTA],
FILTER(
ALLSELECTED('Table'[DATE]),
ISONORAFTER('Table'[DATE], MAX('Table'[DATE]), DESC)
)
)

This is what the data looks like (Note the error on column RT):

Ultimately what I want is to graph that information as follows. I have a slicer that controls the WEEK. When you choose another week, the data changes. On the Line Chart I want the Runnin Total (right now it repeats the data from DELTA).

Thanks for you support! I just can't see how to make it work!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Do you want the running total in a week? If so, modify your RT measure into below one, put the whole 'Calendar' table in ALLSELECTED() function rather than 'Calendar'[Date] column.

RT 2 =
CALCULATE (
    [DELTA],
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        ISONORAFTER ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), DESC )
    )
)

You can also try below measure to calculate the running total.

RT =
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ),
    [Delta]
)

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Anonymous]

WOW! It worked! Thanks so much.

Could you help me understand what was I doing wrong? I would like to learn the logic on the program.

[Anonymous]

-Yes, Table is marked as a Date Table.

-Day and Date column are both in the Date table (see below).

-I have a connection from 2 input Tables to the Data Table (Matching dates). This is to pull the SUM of ENG HOURS respect to each DATE from DATA table, filered by WEEK and expressed as DAY. See below.

This is how the table looks. I use this to check if DAX is doing what I want:

[amitchandak]

@Anonymous , Is Table, marked as the date table?

Are day and date columns coming from the same table?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Please see my reponse below. I had some issues replying to your response. Thanks