Hello all!
I am trying to calculate the correct inventory level, shown by month.
The [QTY] column is a "hard coded" column of the inventory transactions out of D365. This inventory value is only correct if ALL of the dates are summed.
The date DIM_KEY is a chronological number.
The first table shows the DIM_KEY with date and transactions. The other two tables have it shown by YYYY-MM and YYYY.
What I am trying to do is sum based on month/year (selected month/year to show <= DAY(Today() or same period)
For example,
The Inventory Level for the each date would need to calculate all DIM_KEYs <= Selected date
The Inventory Level for 2024-10 would need calculate all DIM_KEYs <= 10/27/2024 (assuming the current date is 11/27/2024).
The Inventory Level for the year of 2023 would need to calculate the all DIM_KEYs <= 10/27/2023.
I hope this makes sense, and someone can help! I can provide any info needed. Thanks!
Hi All,
Firslty lbendlin thank you for your solution!
And @rcb0325 ,According to your description, you want to realize three requirements, respectively, the day of the day, the current month, the previous year's Qty of the month to get the sum of the right?
TotalQuantityYTD =
CALCULATE(
SUM('Table2'[QTY]),
FILTER('Table2',
'Table2'[DATE] >= DATE(YEAR(TODAY()), 1, 1) && 'Table2'[DATE] <= TODAY()
)
)TotalQuantityMonthToDate =
VAR EndMonth = EOMONTH(TODAY(), 0)
VAR Result =
CALCULATE(
SUM('Table2'[QTY]),
FILTER('Table2',
'Table2'[DATE] >= DATE(YEAR(TODAY()), 1, 1) && 'Table2'[DATE] <= EndMonth
)
)
RETURN
ResultTotalQuantityWithYearOffset =
VAR YearOffset = SELECTEDVALUE('YearOffsetTable'[Value], 0)
VAR Result =
CALCULATE(
SUM('Table2'[QTY]),
FILTER('Table2',
'Table2'[DATE] >= DATE(YEAR(TODAY()) + YearOffset, 1, 1) && 'Table2'[DATE] <= TODAY()
)
)
RETURN
Result
The above requirements represent your corresponding three needs, if you want to switch the date freely, you can use selectvalue to replace the today function.
If you have further questions, you can check the pbix file I uploaded, I hope to help you, if I can help you solve the problem I will be touched with great honor!
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rcb0325 ,
According to your needs you want to realize the date from the smallest to the largest for a cumulative, right?
We've made some changes to the code to accomplish your needs.
InventoryLevel YTD =
VAR _YTD =(CALCULATE(
SUM('fact InventoryValueView'[ QTY ]),FILTER(ALL('dim Transaction Date'),
'dim Transaction Date'[Year] <=MAX('dim Transaction Date'[Year]) )
))
RETURN
_YTDInventoryLevel MTD =
VAR _MTD =
(CALCULATE(
SUM('fact InventoryValueView'[ QTY ]),FILTER(ALL('dim Transaction Date'),
'dim Transaction Date'[Date]<=MAX('dim Transaction Date'[Date]))
))
RETURN
_MTD
If you have further questions you can check out my pbix file, I hope it helps and I would be honored if I could solve your problem!
If you still have questions you can check out my pbix file, I hope it helps and I would be honored if I could solve your problem!
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
