cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Running Totals Issue

Hi,

I have imported an Excel table with data recorded periodically (usually weeks), as per screenshot 1.  Some weeks there are no values in the movement, but I still want to show the running total.  I have used the measure below to calculate the running totals, but the dates shown have lost the context of the original 'weekly' dates and are displaying data for every day.  Please note there is a sub-category (Funding Type) that is included in the data.

Is it possible to show the running totals just for the dates in the original data?

10 REPLIES 10
Super User

Hi,

Does this measure work?

``Running Total = if(isblank(SUM(Admissions[Bed Movement])),BLANK(),CALCULATE(SUM(Admissions[Bed Movement]),DATESYTD(Calendar[Date]))``

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Thank you Ashish for your reply.  The solution below has resolved the intial issue, but I need to find a solution to generating running totals where the data is blank for that week.

Community Support

Hi @Anonymous ,

I create a sample to have a test.

Dates table:

``````Dates =
"Weeknum", WEEKNUM ( [Date], 2 )
)``````

Relationship:

Measure:

``````Running Total =
VAR _SUM =
VAR _RUNNING_TOTAL =
IF (
ISBLANK ( _SUM ),
BLANK (),
CALCULATE ( SUM ( Admissions[Bed Movement] ), DATESYTD ( Dates[Date] ) )
)
RETURN
IF (
ISINSCOPE ( Dates[Date] ),
_RUNNING_TOTAL,
IF ( ISINSCOPE ( Dates[Weeknum] ), IF ( ISBLANK ( _SUM ), 0, _RUNNING_TOTAL ) )
)``````

Result is as below.

In week level, matrix will show 0 in week3, due to there is not value in week3.

In Date level:

Best Regards,
Rico Zhou

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

Anonymous
Not applicable

Hi Rico,

Thank you for your response which solves the problem at 'total level' but not for the sub-categories.  With reference to below, I am aiming to show the sub-category running totals for each week so for example, I can the values of the different sub-categories that the 54 for the 3rd January, consists of.

At the moment the running total only shows for those categories where there is a movement value for the week.  I assume if somehow, where there is no value, a zero can be recorded in that week, then this would prompt a running total to be displayed for that category.

Thank you again for your help.

Kind regards,

Keith

Community Support

Hi @Anonymous ,

I think the calculation is based on your data model. Please share a easy sample file without sensitive data. For example, you can share a sample file with data only on 2022/01/01, 2022/01/03. Then show me more a screenshot with the result you want for total and sub-categories levels.

You can tell me more details about the calculate logic about how to get the result. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

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

Anonymous
Not applicable

Hi Rico,

Thank you for the response.  Please find attached a file with sample data and the desired report.  Whilst I have managed to produce a running total, the total is for every calendar day, whereas I am aiming to produce a report for only those dates that appear in the Admissions table.

Thank you again for your help.

Kind regards,

https://1drv.ms/u/s!AkUuPQnVuYsakWg9oovFqtg6Cv-A?e=N7vYj7

Super User

@Anonymous,

The first step is to create a date table. See link below.

Once you have a date table, mark it as a date table, and create a relationship between the Dates and Admissions tables. Then create this measure:

``````Running Total =
CALCULATE (
FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) )
)``````

In the visual, use fields from the Dates table.

Proud to be a Super User!

Anonymous
Not applicable

I have created the date table and followed all the steps in the video and those you suggested, including using the new date table dates on the visual, but the matrix table still shows all dates, rather than just the dates on the admissions table.

Super User

I was able to get it to work using the Admissions date:

``````Running Total =
CALCULATE (
)``````

I believe what was happening originally in your report is that the automatic (built-in) date table was displaying every calendar date in the visual, instead of just the admission dates. Switching to a custom date table essentially replicated this same behavior in the visual. Thus, the revised DAX references the Admissions[Date] column and not the custom date table. However, it's still important to have a custom date table in your data model: you can create a date slicer and filter on year, quarter, etc., and you can perform time intelligence calculations like YTD more easily.

Proud to be a Super User!

Anonymous
Not applicable

Yes, this worked to show the correct dates (thank you), however, the table only shows a running total when there is a bed movement value for that funding type/week.  I would like the running total to display for every funding type, every week, irrespective whether there has been any movement.

I guess this is a separate issue, being that there is no row to even add a zero value to that week because there is no data in the table.

Thank you again for your help.