Migrating from Excel -Calculated Column Help

[Anonymous]

Hi All

First off, im still pretty new to PBI - I am migrating all our excels to PBI and for the most part its a dream and so intuitive. The forum has been such a help for finding ways to do things that excel would do in a formula. So thanks to all the supporters in here. 🙂

For my problem, I have been stumped by one column and i honestly cannot figure out. Myabe becuase i dont undertsand the technicaltities of PBI.

I have a table that shows units recieved and units shipped by month starting from day zero. Along side this i have a Work in Progress column which in excel was quite easy (previous WIP figure + current month Received Figure - Current month Shipped figure = WIP figure) Kind of a running total.

How would i replicate this?

[ryan_mayu]

@Anonymous 

is the screenshot your rawdata? do you want to create a column or measure to achieve this.

[Anonymous]

Hi @ryan_mayu - I wasnt expecting anyone to repsond so fast.

That was my excel version, my PBI version only has the date, recieved and completed in and i would like an additional column that calclated the WIP. 

thank you 🙂

[ryan_mayu]

@Anonymous 

i am a little confused. Your first request is asking to create WIP column. Then what's the calculation logic of receive and complete column.

[Anonymous]

Apologies I'm not sure why you needed to know about the other two columns. The received and shipped columns are taken from a sql query and grouping by month. 

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this to create a column

Column = sumx(FILTER('Table','Table'[month]<=EARLIER('Table'[month])),'Table'[received])-sumx(FILTER('Table','Table'[month]<=EARLIER('Table'[month])),'Table'[completed])

[Anonymous]

It worked! 🙂 Thank you @ryan_mayu  for your help.

 Just a note for anyone else who finds this, you dont put the colmn in the 'Transform Data' section, its in the Data section (within the main PBI window)

[ryan_mayu]

you are welcome