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 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?

 

 

Need Help

10 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

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

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

        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.

  • 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.