Forum Discussion

rcb0325's avatar
rcb0325
Helper I
1 year ago

Getting the correct SUM for Inventory Levels based on DIM_KEY

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 on...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 
    Result
TotalQuantityWithYearOffset = 
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.

 

 

rcb0325's avatar
rcb0325
Helper I
1 year ago

Thank you for your response. 

 

I have tried the calculations you provided, and they do not seem to sum the inventory levels correctly. 

 

I have included two tables below that show the output values, and what the correct values should be. 

 

To provide more clarification, the fact InventoryValue table has a "many to one" relationship to the dim TransactionDate table. 

 

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. 

 

InventoryLevel YTD =
VAR _YTD =
CALCULATE(
    SUM( 'fact InventoryValueView'[Quantity]),
    FILTER( 'dim Transaction Date', 'dim Transaction Date'[ActualDate] >= DATE(YEAR(TODAY()), 1, 1) && 'dim Transaction Date'[ActualDate] <= TODAY()
    )
)
RETURN
_YTD
 
InventoryLevel YTD
YearMax of DateDIM_KEY QTY  InventoryLevel YTD  Correct YTD 
20244749    (577,785.50)                          (577,785.50)         1,766,341.29
20234383    (621,314.21)          2,344,126.79
20224018   2,965,441.00          2,965,441.00
 
InventoryLevel MTD =
VAR _EOM = EOMONTH(TODAY(), 0)
VAR _MTD =
CALCULATE(
    SUM( 'fact InventoryValueView'[Quantity]),
    FILTER( 'dim Transaction Date', 'dim Transaction Date'[ActualDate] >= DATE(YEAR(TODAY()), 1, 1) && 'dim Transaction Date'[ActualDate] <= _EOM
    )
)
RETURN
_MTD
 
InventoryLevel MTD
YYYY-MMMax of DateDIM_KEY QTY  InventoryLevel MTD  Correct MTD 
2024-114718         26,631.50                            26,631.50         1,766,341.29
2024-104688       (83,228.00)                          (83,228.00)         1,739,709.79
2024-094657    (137,987.50)                        (137,987.50)         1,822,937.79
2024-084627         65,781.50                            65,781.50         1,960,925.29
2024-074596       135,291.00                          135,291.00         1,895,143.79
2024-064565         38,303.00                            38,303.00         1,759,852.79
2024-054535       (26,480.00)                          (26,480.00)         1,721,549.79
2024-044504       (12,062.00)                          (12,062.00)         1,748,029.79
2024-034474       (22,471.00)                          (22,471.00)         1,760,091.79
2024-024443       (22,537.00)                          (22,537.00)         1,782,562.79
2024-014414    (539,027.00)                        (539,027.00)         1,805,099.79
2023-124383       (40,870.00)          2,344,126.79
2023-114352           7,149.00          2,384,996.79
2023-104322    (288,001.50)          2,377,847.79
2023-094291         31,523.00          2,665,849.29
2023-084261    (197,243.40)          2,634,326.29
2023-074230    (122,638.00)          2,831,569.69
2023-064199    (117,131.10)          2,954,207.69
2023-054169       (21,942.00)          3,071,338.79
2023-044138       121,751.84          3,093,280.79
2023-034108    (261,740.00)          2,971,528.95
2023-024077       317,751.00          3,233,268.95
2023-014049       (49,923.05)          2,915,517.95
2022-124018   2,965,441.00          2,965,441.00
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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
_YTD
    InventoryLevel 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.