Forum Discussion

benjamin_sasin's avatar
benjamin_sasin
Resolver I
6 years ago
Solved

Cumulative Count Decreases?

Hi,

 

I'm a little confused after running a cumulative count, to find that the number is decreasing at some point:

 

The formula is:

Cumulative Count = CALCULATE(COUNT('Admissions'[email]),FILTER(ALLSELECTED(Admissions),Admissions[startedApplicationAt] <= Max('Admissions'[startedApplicationAt])))

 

The numbers are fine initially:

 

 

But when I filter one category, the number is decreasing in Jan 2020.

 

I can't wrap my mind around how that's even possible. Any clues?

Need Help
  • benjamin_sasin's avatar
    benjamin_sasin
    6 years ago

    Okay I figured it out. As usual it as a silly mistake on my part.

     

    As I observed a mismatch between date a cumulative count, I realise the name fo the date in the formula [startedApplicationAt] wasn't the same as in the report [latest_stage_date], which is the one in the axis of the visual.

     

    So obviously the cumulation wasn't synced with dates... *facepalm*.

     

    Sorry for wasting your time and thanks for sharing your neurons. I probably wouldn't have seen it alone.

5 Replies

    • benjamin_sasin's avatar
      benjamin_sasin
      Resolver I
      6 years ago

      Hi,

       

      I have used the ALLSELECTED() based on examples in this forum. If I remove ALLSELECTED() then the count isn't cumulative anymore:

      (No longer able to insert screenshots for some reason).

       

      As for the data sample: here it is (cleaned up) the column to the right I reconstructed from date parts on the left to order the data. As you can see, the cumulative count drops on January 2020 and there's also another anomaly on december 26 where the count drops to 13 for some reason.

       

      latest_stage_date - Yearlatest_stage_date - Monthlatest_stage_date - DayCumulative Countuser_idCountryAcademic_Yearprogramprogram_orderapplication_stage_sort_orderdatefromparts
      2019April413029Thailand20201.2124-Apr-2019
      2019May2223162India202044222-May-2019
      2019June1433222Bangladesh20201.21214-Jun-2019
      2019July343278Tanzania20202223-Jul-2019
      2019July1953317China20201.21219-Jul-2019
      2019July2163325Palestine202022221-Jul-2019
      2019August2473432Thailand202033224-Aug-2019
      2019September383467Denmark20203323-Sep-2019
      2019September1593529Indonesia20201.21215-Sep-2019
      2019September23103591Myanmar20201.11223-Sep-2019
      2019September26113616Indonesia202033226-Sep-2019
      2019September26123624Pakistan202022226-Sep-2019
      2019October3143667Taiwan20203323-Oct-2019
      2019October11153698Thailand20201.21211-Oct-2019
      2019October14163706Thailand202022214-Oct-2019
      2019October17183722Malaysia202044217-Oct-2019
      2019November10203808China202033210-Nov-2019
      2019November18223876Thailand20201.11218-Nov-2019
      2019December18243866Thailand202033218-Dec-2019
      2019December18253850Papua New Guinea20201.11218-Dec-2019
      2019December20233998Thailand202033220-Dec-2019
      2019December23213860Thailand20201.11223-Dec-2019
      2019December26133638Nigeria20202.22226-Dec-2019
      2019December27264035Thailand202033227-Dec-2019
      2019December28173707Thailand202033228-Dec-2019
      2019December29284055Cambodia20201.21229-Dec-2019
      2019December31193757Thailand202095231-Dec-2019
      2020January2273921Thailand20201.1122-Jan-2020

       

      • benjamin_sasin's avatar
        benjamin_sasin
        Resolver I
        6 years ago

        Okay I figured it out. As usual it as a silly mistake on my part.

         

        As I observed a mismatch between date a cumulative count, I realise the name fo the date in the formula [startedApplicationAt] wasn't the same as in the report [latest_stage_date], which is the one in the axis of the visual.

         

        So obviously the cumulation wasn't synced with dates... *facepalm*.

         

        Sorry for wasting your time and thanks for sharing your neurons. I probably wouldn't have seen it alone.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi benjamin_sasin, 

    Yes, if possible, could you please inform me more detailed infromation for me (such as your sample data or your pbix file)? In addition, you also could check whether the category has negative value in it.

     

    Best Regards,
    Zoe Zhi

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

    • benjamin_sasin's avatar
      benjamin_sasin
      Resolver I
      6 years ago

      Hi I'm sorry, for data privacy reasons I cannot share the pbix file. 

       

      There are no negative categories: it is counting text (emails) against dates.

       

      As with sample data shown above, there is a number of anomalies, where the cumulated count is going down instead of increasing. See dates 26 December or 31 December for example.