Forum Discussion

yazenbarakat123's avatar
yazenbarakat123
Helper I
2 years ago

Running total with blank dates

I am trying to create a running total Line chart visual for the last 3 years , I have an incident table with a date column (blanks are possible if no incidents that month) connected to a calendar table via date column on a one to many relationship. 

my requriments are to create a running total per month and to return the previous months total if there is blanks. however I am struggling having the line stop at the max incident month available in the data set or accounting for the blank by returning the previous month leaving a break in my line chart visual 


  

 


any help would be gladly appreciated!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi yazenbarakat123 ,

    I create a table as you mentioned.

    Then I create a measure as you mentioned.

    Measure = 
VAR _currentdate =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Stock Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= _currentdate )
    )

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • yazenbarakat123's avatar
      yazenbarakat123
      Helper I
      2 years ago


      Hmm  I seem to be running into the same issue where the line cuts off the blank month 
      The Incidents Table does not have any incidents for the month of may so I connect it to a calendar table