Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative Total

I am trying to create a visual which shows the cumulative total. I have tried creating a quick measure using running total, but this is the result I get: My base value is the count, and the fi...
Need Help
  • TheoC's avatar
    TheoC
    3 years ago

    Hi Anonymous 

     

    If it's calculating backwards, you may need to check that your "Month" column is formatted as "Month Year".  Once you do that, you can use a measure like below:

     

    Measure = TOTALYTD ( SUM ( Table[Column] ) , Table[Date] )

     

     

    This is the tabular view:

     

     

    Hope this helps!


    Theo

     

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    I have created a simple smaple, please refer to it to see if it helps you.

    Add an index in Power Query.

    Create a measure.

    Measure = CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[Index]<=SELECTEDVALUE('Table'[Index])))

    Or you can use 'table'[date]<=selectedvalue('table'[date]) replace the index part.

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

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

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hello Theo,

 

Thank you for helping me out with this. However, I am still facing the same issue. 

 

This is how I've amended the formula: 

**bleep** New Cust = TOTALYTD ( COUNTA(Consolidated[New Customer]), Consolidated[Cust Join Date])

 

My date column is formatted in month year format, but my results remain the same.

 

 

Also, if it matters, I am trying to count the number of new customers, and while SUM does not work, COUNTA seems to produce some form of result, even if not in the correct format. I'm still not very sure what is wrong my workings. I really appreciate you helping me through this.

 

TheoC's avatar
TheoC
Community Champion
3 years ago

Anonymous did you try the other option I provided?

 

I'd recommend changing your COUNT to a measure like Count Customers = COUNT ( Consolidated[New Customer] )

 

From there, try either:

 

1.  Measure = TOTALYTD ( [Count Customers] ) , Table[Date] )

 

or

 

2. CALCULATE ( [Count Customers] ) , 'Table'[Date] <= MIN ( 'Table'[Date] ) )

 

Of those don't work, it might be best you provide some dummy data.

 

Best of luck!

 

Theo

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello Theo,

     

    I did give your other solution a try, but no matter what I do, my results remain unchanged.

     

    I'm thinking that the date seems to be the issue here. The date column was obtained using the following codes:

    = Table.AddColumn(#"Added New Cust in FY", "Cust Join Date", each if List.Contains({"95", "96", "97", "98", "99"}, Text.Start([CustID],2))
    then Text.Middle([CustID],2,2) & "/" & "19" & Text.Start([CustID],2)
    else Text.Middle([CustID],2,2) & "/" & "20" & Text.Start([CustID],2))

     

    The date was derieved by concatenating the first 2 digits of a text column along with other texts, then changing the data type to date after the column was created. I experienced no issue while doing so. Perhaps you could identify an issue that I am unable to see regarding my date column? Thank you!