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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Does this measure work?

Running Total = if(isblank(SUM(Admissions[Bed Movement])),BLANK(),CALCULATE(SUM(Admissions[Bed Movement]),DATESYTD(Calendar[Date]))
  • 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.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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