Getting measure totals to calculate properly

[emusat]

Hello, my apologies if I'm double-posting but I think my previous post was marked as spam for some reason.
I've been trying to figure this one out for 2 days. I have the table below, and my goal is to display a running "projected on hand" by week ending. The problem is that for week endings that have no data, it's not displaying any values. Found a fix for that, but it's not summing up properly in the totals. Any suggestions? Thank you in advance!

"Exceptions Summary" Table: 

Item	Week Ending	date	prj_on_hand
A	11/10/2024 12:00:00 AM	11/6/2024	5,683
B	11/10/2024 12:00:00 AM	11/8/2024	13,291
B	11/17/2024 12:00:00 AM	11/15/2024	11,201
B	11/24/2024 12:00:00 AM	11/22/2024	8,783
B	12/1/2024 12:00:00 AM	11/27/2024	9,195
B	12/8/2024 12:00:00 AM	12/2/2024	8,040
B	12/15/2024 12:00:00 AM	12/9/2024	8,707
B	12/22/2024 12:00:00 AM	12/16/2024	9,320
A	1/5/2025 12:00:00 AM	12/31/2024	5,682
B	1/5/2025 12:00:00 AM	12/31/2024	8,115
A	1/12/2025 12:00:00 AM	1/6/2025	5,681
B	1/12/2025 12:00:00 AM	1/6/2025	8,620
B	1/4/2026 12:00:00 AM	12/31/2025	8,620

Measures used to get to where I am now:

Delta_Filldown =

VAR CurrentMonth = MAX ( Calendar[date] )

VAR MaxNonBlankMonth = CALCULATE ( MAX ( 'Exceptions Summary'[date] ), REMOVEFILTERS ( Calendar[date] ), Calendar[date] <= CurrentMonth )

RETURN CALCULATE ( SUM ('Exceptions Summary'[prj_on_hand]),

            REMOVEFILTERS ( Calendar[date] ),

            Calendar[date] = MaxNonBlankMonth )

Summary Delta =

IF(HASONEFILTER('Exceptions Summary'[item]),

    [Delta_Filldown],

    SUMX (

        VALUES ( 'Exceptions Summary'[item] ),

        MAX ( 0, [Delta_Filldown] )

    )

)

Current Result:

[Whitewater]

This is better for MATRIX:

Last Stock =

VAR PreDate=

CALCULATE(

        MAX('Exceptions Summary'[date]),

        FILTER(

            ALLEXCEPT('Exceptions Summary','Exceptions Summary'[prj_on_hand]),

            'Exceptions Summary'[date]<MAX('Exceptions Summary'[date])

        )

)

RETURN

IF(HASONEVALUE(Dates[WeekEnding]), IF(MIN('Exceptions Summary'[prj_on_hand])>0,

MIN('Exceptions Summary'[prj_on_hand]),

CALCULATE(

    MIN('Exceptions Summary'[prj_on_hand]),

    FILTER(ALLEXCEPT('Exceptions Summary','Exceptions Summary'[prj_on_hand]), 'Exceptions Summary'[date] = PreDate)

)

))

[Whitewater]

Hello:
If you have a data table connected to your date. I also filtered to year 2024 & 2025. You can try this:

Last Stock =

VAR PreDate=

CALCULATE(

        MAX('Exceptions Summary'[date]),

        FILTER(

            ALLEXCEPT('Exceptions Summary','Exceptions Summary'[prj_on_hand]),

            'Exceptions Summary'[date]<MAX('Exceptions Summary'[date])

        )

)

RETURN

IF(HASONEVALUE('Exceptions Summary'[Item]), IF(MIN('Exceptions Summary'[prj_on_hand])>0,

MIN('Exceptions Summary'[prj_on_hand]),

CALCULATE(

    MIN('Exceptions Summary'[prj_on_hand]),

    FILTER(ALLEXCEPT('Exceptions Summary','Exceptions Summary'[prj_on_hand]), 'Exceptions Summary'[date] = PreDate)

)

))

[Anonymous]

Hi @emusat ,

Would you be able to kindly share a sample of your data tables and PBIX file? (Please ensure that any sensitive data is deleted)

Best Regards,

Bof

[emusat]

Hi @Anonymous it won't let me upload the pbix file here but please see the link below. It has the tables, visuals, and measures in there

https://wetransfer.com/downloads/2438dbc4cf76b33071d12e30d87c12cc20241111163205/90345e?t_exp=1731601925&t_lsid=f6b01099-eb05-46e5-8c98-4d0fe5f47c3d&t_network=link&t_rid=YXV0aDB8NjczMjMwNzhkYmRmOWNkOTk1ZTM0MGIy&t_s=download_link&t_ts=1731342725

[Anonymous · Accepted solution]

Hi @emusat ,

Please try:

Summary Delta = 
IF(
    HASONEFILTER('Exceptions Summary'[item]),
    [Delta_Filldown],
    IF(
        ISFILTERED('Exceptions Summary'[item]),
        SUMX(
            VALUES('Exceptions Summary'[item]),
            MAX(0, [Delta_Filldown])
        ),
        if(SUMX(
            ALL('Exceptions Summary'[item]),
            MAX(0, [Delta_Filldown])
        )=0,BLANK(),SUMX(
            ALL('Exceptions Summary'[item]),
            MAX(0, [Delta_Filldown])
        ))
    )
)

Best Regards,

Bof

[emusat]

Thank you so much, that did the trick. Now I ran into a different issue I didn't realise I'd have- was wondering if you have any ideas for this one. For the dates that have no previous values to carry over, is it possible to return an average or max of on_hand?

Updated PBIX below:
https://we.tl/t-bb2aKzsIAV

[emusat]

Nevermind, I figured this one out. Thank you so much for your help!

[VahidDM]

Hi @emusat 

Your current measure [Delta_Filldown] fills in the projected on-hand values for week endings with no data, but the totals are not summing up correctly. This happens because the measure calculates correctly at the row level but doesn't aggregate properly at the total level due to differences in filter context.

Solution:

To fix this, you can adjust your measure to iterate over each Item and calculate the projected on-hand value individually. This ensures that the totals are the sum of the individual projected values for each item, providing accurate totals.

Revised Measure:

ProjectedOnHand =

SUMX(

    VALUES('Exceptions Summary'[Item]),

    VAR CurrentItem = 'Exceptions Summary'[Item]

    VAR CurrentDate = MAX('Calendar'[date])

    VAR MaxDateWithData =

        CALCULATE(

            MAX('Exceptions Summary'[date]),

            ALL('Calendar'),

            'Exceptions Summary'[Item] = CurrentItem,

            'Exceptions Summary'[date] <= CurrentDate

        )

    VAR ProjectedValue =

        CALCULATE(

            SUM('Exceptions Summary'[prj_on_hand]),

            'Exceptions Summary'[Item] = CurrentItem,

            'Exceptions Summary'[date] = MaxDateWithData

        )

    RETURN

        ProjectedValue

)

Explanation:

• SUMX Function:
• Iterates over each unique Item in your data.
• Variables:
• CurrentItem: Holds the current item in the iteration.
• CurrentDate: The date in the current context (from your Calendar table).
• MaxDateWithData: Finds the latest date with data for the current item up to CurrentDate.
• ProjectedValue: Calculates the projected on-hand value for the current item on MaxDateWithData.
• Return Value:
• The measure returns the sum of ProjectedValue for all items, ensuring accurate totals.

Implementation Steps:

1. Ensure Proper Relationships:
• Your Calendar table should be properly related to the 'Exceptions Summary' table via the date fields.
2. Use the Calendar Date in Your Visual:
• When creating your report, use Calendar[date] or Calendar[Week Ending] as the axis or row labels to ensure the date context is correctly applied.
3. Replace Column Names if Necessary:
• Ensure that the column names in the measure (Item, date, prj_on_hand) match exactly those in your data model.

Why This Works:

• Correct Filter Context:
• By using ALL('Calendar') in MaxDateWithData, you remove any filters on the date, allowing you to find the latest date with data regardless of the current date filter.
• Accurate Totals:
• SUMX ensures that the calculation is performed for each item individually, and then sums up the results, which produces correct totals at all levels.
• Handling Missing Data:
• The measure accounts for weeks with no data by carrying forward the last known projected on-hand value.

Additional Notes:

• Performance Consideration:
• While SUMX is powerful, it can be less performant on very large datasets. Monitor the performance and optimize your data model if necessary.
• Validation:
• After implementing the measure, validate the results by comparing them with expected values to ensure accuracy.

Conclusion:

By adjusting your measure to calculate the projected on-hand value per item and summing these up, you resolve the issue with incorrect totals. This approach ensures that your report accurately reflects the running projected on-hand inventory by week ending, even when data is missing for certain weeks.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[emusat]

Thank you for your suggestion @VahidDM. Unfortunately this is the result I get using your measure, which doesn't carry over the last projected on hand value for items with no data for the given dates

This is what I'm trying to accomplish: