Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
@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 |
Hi,
You may download my PBI fle from here.
Hope this helps.
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] )
)
You might as well start a new thread.
Not required, solution found - more a follow up to say thankyou for your input because it helped me find my solution.
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |