Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 tota...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.