Running Total by Year and By Plant Issue

[karabryan]

I am fairly new to PowerBI and have seen the topics on running totals by year.  I am using the following but don't get a running total every time and when it does work (it will work at points and then revert back to unsummed data), I am not able to use it in another calculation because it does not appear as a field to select from.  I am trying to calculate inventory turns.

Here is the table format:

Company | DateTable.SortDate (I've also tried DateTable.Date) | InvTurnsInv | InvTurnsCOGS

I need a column that returns the running total of the InvTurnsInv column by year for each company.  I also need another column that allows me to use that new column in an inventory turns calculation.

My date table is linked to my InvTurns Data table.

I am using the following formula:

InvTurnsYTD =

TOTALYTD(SUM('InventoryTurnsCOGS'[INVTURNSINV]), 'DateTable'[Date])

This worked for me in a table where I needed a running total on Accounts Receivables but same thing - I can't use this running total in a formula for another calculated field.

Any help would be appreciated!!

[parry2k]

@karabryan sorry not sure what you mean by running total not work for another calculated columns, please provide more details. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[karabryan]

When writing the formula for the calculated field that will use the running total calculated field, I don't see that column name available in the drop down of fields associated with my table to use in my formula.

[parry2k]

@karabryan can you share sample data and expected out and how you tables are related if there is more than one. It will help to get you the solution.

[karabryan]

InvBalTurns3 = TOTALYTD(SUM(InvTurnsCOGS[INVTURNSINV]), 'DateTable'[Date]))

1 to Many relationship between DateTable and InvTurnsCOGS table with Date being the matching field in both tables

Column headers are as follows:

InvTurnsCOGSCompany    InvTurnsCOGSSortDate     InvTurnsCOGSINVTURNSINV     INVTURNSCOGS     InvBalTurns3

Wurtec                                          2/1/2019                              100                                            10                   YTD Formula above

Wurtec                                          3/1/2019                              -50                                             11         

WURCAN                                       2/1/2019                              90                                             9

WURCAN                                       3/1/2019                              20                                              11

Due to company policy, I can't share the data I'm working with so hopefully this helps.  I want to sum InvTurnsCOGSINVTURNSINV YTD for each company and over each year (my table has data from 2016-current day).  The results will live in the INVTURNSCOGS column.  I then need to add a new column to look at the average InvBalTurns3 on a rolling 3-month basis so that I can add another column and calculate inventory turns.

@parry2k