Running Totals Issue

[Anonymous]

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?

[Ashish_Mathur]

Hi,

Does this measure work?

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

[Anonymous]

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.

[v-rzhou-msft]

Hi @Anonymous ,

I create a sample to have a test.

Dates table:

Dates =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Admissions'[Date] ), MAX ( 'Admissions'[Date] ) ),
    "Weeknum", WEEKNUM ( [Date], 2 )
)

Relationship:

Measure:

Running Total = 
VAR _SUM =
    SUM ( Admissions[Bed Movement] )
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]

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

[v-rzhou-msft]

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]

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

[DataInsights]

@Anonymous,

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

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 

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 (
    SUM ( Admissions[Bed Movement] ),
    FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) )
)

In the visual, use fields from the Dates table.

[Anonymous]

Thank you for replying.

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.

[DataInsights]

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

Running Total = 
CALCULATE (
    SUM ( Admissions[Bed Movement] ),
    FILTER ( ALLSELECTED ( Admissions[Date] ), Admissions[Date] <= MAX ( Admissions[Date] ) )
)

In the visual, use Admissions[Date]:

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.

[Anonymous]

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.