SumX for Product Value based on Max Date

[transform99]

I have a warehouse report that shows stock levels.

I have the following formula that is aiming to return 1 of 2 values:

If the [Reporting Date] is filtered then show the [product value] based on the [Reporting Date], else show the [product value] based on the most recent [Reporting Date].

WHS Value = 
VAR _ReportDate =
    MAX ( 'Table1'[Reporting Date] )

RETURN
    IF (
        ISFILTERED ( 'Table1'[Reporting Date] ) = FALSE (),
        SUMX (
            FILTER ( 'Table1', _ReportDate = 'Table 1'[Reporting Date] ),
            'Table1'[Product Value]
        ),
        SUM ( 'Table1'[Product Value] )
    )

My issue is that if there is no stock in the warehouse it's a null value, and the formula displayed is showing historic data from older snapshots.

How can I amend this formula so that where products have no stock (and the report is null) - it excludes them from the formula when no report date is selected?

[Ashish_Mathur]

Hi,

Share some data (in a format that can be pasted in an MS Excel file), explain the question and show the expected result. in a simple Table format.

[transform99]

@Ashish_Mathur below is a pivot table summary with the current result vs expected result. I've also included a raw example which can be pivoted.

This is essentially an exercise in handling nulls/blanks where stock is not present at certain times.

	SOH			Current Result	Expected Result
Product ID	21/11/2022	19/12/2022	16/01/2023
10015122	18	14		14	0
10015775	2	2		2	0
10023567	3890			3890	0
10024806	4	4		4	0
10024881	1			1	0
10027491	4			4	0
10002131	13	13	13	13	13
10002186	30	34	15	15	15
10002189	9	9	3	3	3
10002190	11	13	14	14	14
10002195	27	35	18	18	18
10002196	11	12	13	13	13

Raw Extract:

Product Number	Unit Of Measure	SOH	Report Run Date
10015122	EA	18	21/11/2022
10015775	EA	2	21/11/2022
10023567	MT	890	21/11/2022
10023567	MT	2000	21/11/2022
10023567	MT	1000	21/11/2022
10024806	EA	4	21/11/2022
10027491	EA	4	21/11/2022
10002189	EA	3	21/11/2022
10002189	EA	5	21/11/2022
10002189	EA	1	21/11/2022
10002190	EA	6	21/11/2022
10002190	EA	2	21/11/2022
10002190	EA	3	21/11/2022
10015122	EA	14	19/12/2022
10015775	EA	2	19/12/2022
10024806	EA	4	19/12/2022
10002189	EA	4	19/12/2022
10002189	EA	4	19/12/2022
10002189	EA	1	19/12/2022
10002190	EA	8	19/12/2022
10002190	EA	3	19/12/2022
10002190	EA	2	19/12/2022
10002189	EA	2	16/01/2023
10002189	EA	1	16/01/2023
10002190	EA	8	16/01/2023
10002190	EA	4	16/01/2023
10002190	EA	2	16/01/2023

[Ashish_Mathur]

Hi,

You may download my PBI fle from here.

Hope this helps.

[transform99]

Hi Ashish,

Sorry it's taken me so long to get back to this but I couldn't get this to work.

I have 2 tables that follow the exact same path as the data I have provided to you, but in 1 table it has a Subcontractor ID instead of a Product ID and it was still showing values that were historic from old reporting periods.

The issue I was facing was that regardless of the MaxDate that was coming through from [Report Run Date] or Calendar[Date] in your example - When applying the measure it was assessing it individually per product ID / Subcontractor ID.

The solution I found was to use a calculated column:

WHS MaxDate = Max([Report Run Date])

The following worked:

WHS Value = 
    IF (
        ISFILTERED ( 'Table1'[Reporting Date] ) = FALSE (),
        SUMX (
            FILTER ( 'Table1', [WHS MaxDate] = 'Table 1'[Reporting Date] ),
            'Table1'[Product Value]
        ),
        SUM ( 'Table1'[Product Value] )
    )

[Ashish_Mathur]

You might as well start a new thread.

[transform99]

Not required, solution found - more a follow up to say thankyou for your input because it helped me find my solution.

[Ashish_Mathur]

You are welcome.