Forum Discussion
Sum/ Max - Calculation based on date [Stock calculation]
Hello all,
I am totaly new one with PowerBi DAX function. I already have it one post ( solved ) but now need the upgrade the function but dont know how to achiev.
All the Dates values are in format ( DD-MM-YYYY)
From Original post have this measures in my report:
Just for take max value from the data sheet [Always will be there just one value] - Stock is downloaded once per week
Total_Stock = MAX(Planning_Material_Delfor[Stock_Sum])
Summarize the value each day:
Total_Demand = CALCULATE(SUM(
Planning_Material_Delfor[Column]),
FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <=MAX('Calendar'[Date])
)
)
Final measure called "Final stock" or like "Balance Stock"
Final Stock = [Total_Stock] - [Total_Demand]
This measures works fine, but there is issue with Demand and Calendar table, beacause the first date is earlier this year - for example: Stock is from 23.10.2023 and Demands: 01.05.2023 that mean i got wrong data in actual date/week.
I already try to somehow "filtered" like this:
Measure_Test_Demand = CALCULATE(CALCULATE(SUM(Planning_Material_Delfor[Column]),FILTER(ALL('Calendar'[Date]),
'Calendar'[Date] = MAX('Calendar'[Date]))),'Calendar'[Week Offset] >= 0)Final_Stock_Test = [Total_Stock] - [Measure_Test_Demand]
This works only in first monday ( 23.10.2023)
Example how works now:
| Date | Stock | Demands | Material | Balance Stock |
| 23/10/2023 | 50 | 10 | Material - A | 40 |
| 24/10/2023 | 50 | 0 | Material - A | 50 (default stock value) |
| 30/10/2023 | 50 | 5 | Material - A | 45 (wrong value) |
So correct result there must be "35" at 30/10/2023
Thank you so much for all any ideas!
Have a nice day
See the original post, where was all the funcition was provided by parry2k
Original post:
5 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hello,
There is link for the .pbix file.Perhaps will be more clear.
https://drive.google.com/file/d/1Nf37G80idnu9JW9ODKtBSOtr1LEQ2uhG/view?usp=sharing
Demands are in the past too [like 01/05/2023] but stock is downloaded every day so i dont need calculate "Current stock" - "Demand" when the stock is from 01/10/2023 but demands are in the past, i just need to take from actual Monday > Future
(understood not screenshot. it will be for just better understanding)
Sum of Stock: have same QTY ( because is downloaded for example: once per week)
Date: is from calendar table and connection with Demand dateSum of Demand: is calculated like this:
CALCULATE(SUM( Planning_Material_Delfor[Column]), FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <=MAX('Calendar'[Date]) ) )Balance stock: Its just measure
Total Stock - Total DemandAll i want is just ignore the past dates and just look only actual and more
(Start calculate from actual week, that was try to use week offset )
Thank you so much
What's the point of computing Total Stock? Stock is something that only has a meaning for each individual day.
How can Balance Stock be negative? Are you selling things that are not there?
Greetings all,
I just modify the original one funtion to this:
Total_Demand_CurrentWeek = CALCULATE(SUM( Planning_Material_Delfor[Column]), FILTER(ALL('Calendar'[Week Offset]),'Calendar'[Week Offset] >= 0) )But can someone help me there, why its just same value like in column
"Planning_Material_Delfor[Column]" ( In that column i have it at 13/11/2023 - 76pcs and that measure show 76pcs. but the correct result is 52 + 76 = 128 because week before (06/11/2023) is there 52pcs.
Thank you
