DAX funciton for initial sotck

[Anonymous]

Hello, I have a table where i have my initial stock for my current month, antoher table where i get my unit sales and another tabel where i get my purchases.

To calculate my final stock of the month i calculate initial stock - sales + purchases.

I only have my initial stock for the first month(current month), for the others months the inital stock must be the final stock of previous month.

For my sales and purchases i have data of all the year.

I need help with a function that takes lasts months final stock and uses it as the new initial stock.

[maymaranhao]

Oh! I see... I just want to understand how do you got the total value on the first line of the beginning cash. I did balance but something is going wrong. If you can enlighten me I'll be really glad!

[amitchandak]

@Anonymous , do have data for all the dates. If so you can use

https://docs.microsoft.com/en-us/dax/openingbalancemonth-function-dax

https://docs.microsoft.com/en-us/dax/closingbalancemonth-function-dax

openingbalancemonth(Sum(Table[Value]), Date[Date])
closingbalancemonth(Sum(Table[Value]), Date[Date])

closingbalancemonth last month =closingbalancemonth(Sum(Table[Value]), dateadd(Date[Date],-1, Month))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[ryan_mayu]

@Anonymous 

Here is some suggestion for you.

Create a new column to calculate stock and transfer sales to negative value.

newamount = 
VAR stock=CALCULATE(sum('Table'[amount]),FILTER('Table','Table'[month]<=EARLIER('Table'[month])))
return if('Table'[month]<>min('Table'[month])&&'Table'[type]="stock",stock,if('Table'[type]="sales",'Table'[amount]*-1,'Table'[amount]))

I did something about financial balance which is quite similar to your request. Hope this is helpful.

[maymaranhao]

@ryan_mayu  How did you manage to activate the total row value as the same value on the Beginning cash row?

[ryan_mayu]

@maymaranhao 

it's too early and I didn't attach the attachment. I think the balance is the beginning. You can create a new post with your sample data and expected output.