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.

     

     

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!