Cumulative Running Count - Trend Line Graph

[Charuta]

Hello,

Need help - For a cumulative trend line chart - open vs closed tickets. doing a running count using this formula - 

Running Total = CALCULATE((count(Data[ticketcount])),Filter(All(Data[createddate]),Data[createddate]<=Max(Data[createddate])) )

Where there is blank, it should be showing previous month data. For example, for the month of May there is no ticket Closed, we need to show same count as what is seen in April. Closed line is breaking and not showing any data for May.

I tried using Calendar table, but unable to get the right output. When there is no data for the month, the line breaks in between.  

Any suggestions for this?

[Anonymous]

Hi @Charuta ,

According to my understanding, when there is no data in this month, then use the count number of previous month,right?

Please firstly create a new calendar table and build a relationship between two tables:

Calendar = CALENDAR(MIN('Data'[CreatedDate]),MAX('Data'[CreatedDate]))

Then use the following formula to create a measure:

Measure = 
var _count=CALCULATE(COUNT(Data[TicketCount]),FILTER('Data', FORMAT([CreatedDate],"YYYY-MM")=FORMAT(MAX('Calendar'[Date]),"YYYY-MM"))) 
return IF(_count=BLANK(),CALCULATE(COUNT(Data[TicketCount]),PREVIOUSMONTH('Calendar'[Date])),_count)

The final output is shown below:

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

[Charuta]

Thanks both , @Anonymous and @amitchandak - Calendar date option worked, the format was date/time earlier so it wasn't working. Now it is all fixed

[amitchandak · Accepted solution]

@Charuta , Have you tried like with date table

for active join created date

Running Total = CALCULATE((count(Data[ticketcount])),Filter(All(Date[Date]),Date[Date]<=Max(Date[Date])) )

[Charuta]

Hi Amit,

Yes, I have tried this. It does not give the increasing trend and no data for the next month. still shows blank.