Forum Discussion

Cienfu's avatar
Cienfu
Regular Visitor
6 years ago

Cumulative chart with empty dates

Hello, 

I'm trying to create a commulative graph where I would indicate the evolution of a process consisting in requesting an item, approve the item, and then execute the action. 

The idea would be that the first day only one item has been requested, but nothing yet approved or executed. 

By day 6 for example, 6 have been requested, 4 approved and 2 executed. 

By day 10, 10 requested, 7 approved 4 executed. 

 

ItemDate requestedDate approvedDate executed
Item 101/01/202005/01/202006/01/2020
Item 202/01/202005/01/202006/01/2020
Item 303/01/202005/01/202008/01/2020
Item 404/01/202005/01/202009/01/2020
Item 505/01/202007/01/202012/01/2020
Item 606/01/202007/01/2020 
Item 707/01/202009/01/2020 
Item 808/01/2020  
Item 909/01/2020  
Item 1010/01/2020  

I have tried with the formula below, but the result was not correct. 

 

Cummulative items approved= CALCULATE(
COUNTA(Data[Item]),
FILTER(
ALL(Data),
Data[Date approved]<=MAX(Data[Date approved])))

 

Would you know what am I missing? 

Thanks a lot! 

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    When you say 'the result was not correct', do you mean the rows with no approved date were included in the count?

    If so, you can add a clause :

    && NOT(ISBLANK(Data[Date approved])
    • Cienfu's avatar
      Cienfu
      Regular Visitor
      6 years ago

      Thanks! it works fine individually:

      If I create a graphic for each measure (axis X date requested/approved/converted and Y number of items it shows correct data. 

      However, when I try to combine them into just one graph, the result is totally messed up. I think it's because of the dates, since some dates are existing in one column but not in others