Getting the correct SUM for Inventory Levels based on DIM_KEY

Question

Hello all!I am trying to calculate the correct inventory level, shown by month.&nbsp;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.&nbsp;The date DIM_KEY is a chronological number.&nbsp;&nbsp;The first table shows the DIM_KEY with date and transactions. The other two tables have it shown by YYYY-MM and YYYY.&nbsp;&nbsp;What I am trying to do is sum based on month/year (selected month/year to show &lt;= DAY(Today() or same period)For example,The Inventory Level for the each date would need to calculate all DIM_KEYs &lt;= Selected dateThe Inventory Level for 2024-10 would need calculate all DIM_KEYs &lt;= 10/27/2024 (assuming the current date is 11/27/2024).&nbsp;The Inventory Level for the year of 2023 would need to calculate the all DIM_KEYs &lt;= 10/27/2023.&nbsp;I hope this makes sense, and someone can help! I can provide any info needed. Thanks!&nbsp;&nbsp;&nbsp;

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? 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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

anonymous · Answer

Hi All,Firslty&nbsp; lbendlin&nbsp;thank you for your solution!And&nbsp;rcb0325&nbsp;,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] &gt;= DATE(YEAR(TODAY()), 1, 1) &amp;&amp; 'Table2'[DATE] &lt;= TODAY()
        )
    )TotalQuantityMonthToDate = 
VAR EndMonth = EOMONTH(TODAY(), 0)  
VAR Result = 
    CALCULATE(
        SUM('Table2'[QTY]),
        FILTER('Table2', 
            'Table2'[DATE] &gt;= DATE(YEAR(TODAY()), 1, 1) &amp;&amp; 'Table2'[DATE] &lt;= EndMonth
        )
    )
RETURN 
    ResultTotalQuantityWithYearOffset = 
VAR YearOffset = SELECTEDVALUE('YearOffsetTable'[Value], 0)  
VAR Result = 
    CALCULATE(
        SUM('Table2'[QTY]),
        FILTER('Table2', 
            'Table2'[DATE] &gt;= DATE(YEAR(TODAY()) + YearOffset, 1, 1) &amp;&amp; 'Table2'[DATE] &lt;= 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&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

rcb0325 · Answer

Thank you for your response.&nbsp;&nbsp;I have tried the calculations you provided, and they do not seem to sum the inventory levels correctly.&nbsp;&nbsp;I have included two tables below that show the output values, and what the correct values should be.&nbsp;&nbsp;To provide more clarification, the fact InventoryValue table has a "many to one" relationship to the dim TransactionDate table.&nbsp;&nbsp;Here are the formulas you provided, but with the correct table names. I do not have a "Year Offset" table, so am unable to include the third formula.&nbsp;&nbsp;InventoryLevel YTD =VAR _YTD = CALCULATE(&nbsp; &nbsp; SUM( 'fact InventoryValueView'[Quantity]), &nbsp; &nbsp; FILTER( 'dim Transaction Date', 'dim Transaction Date'[ActualDate] &gt;= DATE(YEAR(TODAY()), 1, 1) &amp;&amp; 'dim Transaction Date'[ActualDate] &lt;= TODAY()&nbsp; &nbsp; ))RETURN_YTD&nbsp;InventoryLevel YTDYearMax of DateDIM_KEY&nbsp;QTY&nbsp;&nbsp;InventoryLevel YTD&nbsp;&nbsp;Correct YTD&nbsp;20244749&nbsp;&nbsp;&nbsp; (577,785.50)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (577,785.50)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,766,341.2920234383&nbsp;&nbsp;&nbsp; (621,314.21)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,344,126.7920224018&nbsp;&nbsp; 2,965,441.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,965,441.00&nbsp;InventoryLevel MTD =VAR _EOM = EOMONTH(TODAY(), 0)VAR _MTD = CALCULATE(&nbsp; &nbsp; SUM( 'fact InventoryValueView'[Quantity]), &nbsp; &nbsp; FILTER( 'dim Transaction Date', 'dim Transaction Date'[ActualDate] &gt;= DATE(YEAR(TODAY()), 1, 1) &amp;&amp; 'dim Transaction Date'[ActualDate] &lt;= _EOM&nbsp; &nbsp; ))RETURN_MTD&nbsp;InventoryLevel MTDYYYY-MMMax of DateDIM_KEY&nbsp;QTY&nbsp;&nbsp;InventoryLevel MTD&nbsp;&nbsp;Correct MTD&nbsp;2024-114718&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,631.50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 26,631.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,766,341.292024-104688&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (83,228.00)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (83,228.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,739,709.792024-094657&nbsp;&nbsp;&nbsp; (137,987.50)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (137,987.50)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,822,937.792024-084627&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65,781.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65,781.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,960,925.292024-074596&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 135,291.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 135,291.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,895,143.792024-064565&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 38,303.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 38,303.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,759,852.792024-054535&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (26,480.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (26,480.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,721,549.792024-044504&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (12,062.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (12,062.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,748,029.792024-034474&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (22,471.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (22,471.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,760,091.792024-024443&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (22,537.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (22,537.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,782,562.792024-014414&nbsp;&nbsp;&nbsp; (539,027.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (539,027.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,805,099.792023-124383&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (40,870.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,344,126.792023-114352&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,149.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,384,996.792023-104322&nbsp;&nbsp;&nbsp; (288,001.50)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,377,847.792023-094291&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31,523.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,665,849.292023-084261&nbsp;&nbsp;&nbsp; (197,243.40)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,634,326.292023-074230&nbsp;&nbsp;&nbsp; (122,638.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,831,569.692023-064199&nbsp;&nbsp;&nbsp; (117,131.10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,954,207.692023-054169&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (21,942.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,071,338.792023-044138&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 121,751.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,093,280.792023-034108&nbsp;&nbsp;&nbsp; (261,740.00)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,971,528.952023-024077&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 317,751.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,233,268.952023-014049&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (49,923.05)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,915,517.952022-124018&nbsp;&nbsp; 2,965,441.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,965,441.00&nbsp;

anonymous · Answer

Hi&nbsp;rcb0325&nbsp;,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] &lt;=MAX('dim Transaction Date'[Year]) )
    ))
RETURN
_YTDInventoryLevel MTD = 
VAR _MTD =
(CALCULATE(
        SUM('fact InventoryValueView'[ QTY ]),FILTER(ALL('dim Transaction Date'),
        'dim Transaction Date'[Date]&lt;=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&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

Forum Discussion

Getting the correct SUM for Inventory Levels based on DIM_KEY

4 Replies

Related content

Inventory and Stock balance calculation

Correcting Data

Inventory Management Dashboard

Inventory Forecast

Dax correction

Year	Max of DateDIM_KEY	QTY	InventoryLevel YTD	Correct YTD
2024	4749	(577,785.50)	(577,785.50)	1,766,341.29
2023	4383	(621,314.21)		2,344,126.79
2022	4018	2,965,441.00		2,965,441.00

YYYY-MM	Max of DateDIM_KEY	QTY	InventoryLevel MTD	Correct MTD
2024-11	4718	26,631.50	26,631.50	1,766,341.29
2024-10	4688	(83,228.00)	(83,228.00)	1,739,709.79
2024-09	4657	(137,987.50)	(137,987.50)	1,822,937.79
2024-08	4627	65,781.50	65,781.50	1,960,925.29
2024-07	4596	135,291.00	135,291.00	1,895,143.79
2024-06	4565	38,303.00	38,303.00	1,759,852.79
2024-05	4535	(26,480.00)	(26,480.00)	1,721,549.79
2024-04	4504	(12,062.00)	(12,062.00)	1,748,029.79
2024-03	4474	(22,471.00)	(22,471.00)	1,760,091.79
2024-02	4443	(22,537.00)	(22,537.00)	1,782,562.79
2024-01	4414	(539,027.00)	(539,027.00)	1,805,099.79
2023-12	4383	(40,870.00)		2,344,126.79
2023-11	4352	7,149.00		2,384,996.79
2023-10	4322	(288,001.50)		2,377,847.79
2023-09	4291	31,523.00		2,665,849.29
2023-08	4261	(197,243.40)		2,634,326.29
2023-07	4230	(122,638.00)		2,831,569.69
2023-06	4199	(117,131.10)		2,954,207.69
2023-05	4169	(21,942.00)		3,071,338.79
2023-04	4138	121,751.84		3,093,280.79
2023-03	4108	(261,740.00)		2,971,528.95
2023-02	4077	317,751.00		3,233,268.95
2023-01	4049	(49,923.05)		2,915,517.95
2022-12	4018	2,965,441.00		2,965,441.00