Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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!
@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.
@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.
Proud to be a Super User!
@ryan_mayu How did you manage to activate the total row value as the same value on the Beginning cash row?
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.
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |