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.

 

 

 

  • 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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

        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