SumX for Product Value based on Max Date

Question

I have a warehouse report that shows stock levels.&nbsp;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].&nbsp;&nbsp;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] )
    )&nbsp;&nbsp;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?&nbsp;&nbsp;

ashish_mathur · Answer

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.

anonymous · Answer

Ashish_Mathur&nbsp;below is a pivot table summary with the current result vs expected result. I've also included a raw example which can be pivoted.&nbsp;This is essentially an exercise in handling nulls/blanks where stock is not present at certain times.&nbsp;&nbsp;SOH&nbsp;&nbsp;Current ResultExpected ResultProduct ID21/11/202219/12/202216/01/2023&nbsp;&nbsp;100151221814&nbsp;1401001577522&nbsp;20100235673890&nbsp;&nbsp;389001002480644&nbsp;40100248811&nbsp;&nbsp;10100274914&nbsp;&nbsp;401000213113131313131000218630341515151000218999333100021901113141414100021952735181818100021961112131313&nbsp;Raw Extract:Product NumberUnit Of MeasureSOHReport Run Date10015122EA1821/11/202210015775EA221/11/202210023567MT89021/11/202210023567MT200021/11/202210023567MT100021/11/202210024806EA421/11/202210027491EA421/11/202210002189EA321/11/202210002189EA521/11/202210002189EA121/11/202210002190EA621/11/202210002190EA221/11/202210002190EA321/11/202210015122EA1419/12/202210015775EA219/12/202210024806EA419/12/202210002189EA419/12/202210002189EA419/12/202210002189EA119/12/202210002190EA819/12/202210002190EA319/12/202210002190EA219/12/202210002189EA216/01/202310002189EA116/01/202310002190EA816/01/202310002190EA416/01/202310002190EA216/01/2023

ashish_mathur · Answer

Hi,
You may download my PBI fle from here.
Hope this helps.

anonymous · Answer

Hi Ashish,Sorry it's taken me so long to get back to this but I couldn't get this to work.&nbsp;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.&nbsp;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.&nbsp;The solution I found was to use a calculated column:WHS MaxDate = Max([Report Run Date])&nbsp;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 · Answer

You might as well start a new thread.

Forum Discussion

SumX for Product Value based on Max Date

7 Replies

Related content

Segmentation based on Product List available for each Customer

Getting value for previous year based on value name and year slicer

Production Lessons

Product Counts Based on Measure Values

Values not averaging correctly based on date

	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

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