I’m having a hard time trying to get the right answer and I thought that someone can help me out.
I’m trying to obtain the Date Difference between the Expiration Date and Current Day of all the Subproducts while removing the Subproducts with an Inventory Level of fewer than 0 units.
Here’s the measure I came out with:
Product Life =
VAR InventoryTable =
SUMMARIZECOLUMNS (
Subproducts[Subproduct ID],
Subproducts[Expiration Date],
“Units Inventory”, [Units Inventory],
“Current Date”, TODAY ()
)
VAR GreaterthanZero =
FILTER ( InventoryTable, [Units Inventory] > 0 ) //Inventory of Subproducts greater than Zero
RETURN
SUMX (
GreaterthanZero,
DATEDIFF ( TODAY (), MAX ( Subproducts[Expiration Date] ), MONTH )
)
I get a value but every time I added the measure with the Subproduct ID I get an error.
I need to get these values in a Measure, not in a Calculated Column though.
@Yeriel Nothing wrong with your code but that’s a problem with SUMMARIZECOLUMNS, it can’t be executed in a filter context modified by context transition. You have to use ADDCOLUMNS/SUMMARIZE construct.
Do you know how can I use the SelectedValue to bring the values from the Virtual table instead of SumX or another measure that you may think of?
Because I don´t need the Total Values for Product Life.
Product Life =
VAR InventoryTable =
ADDCOLUMNS (
SUMMARIZE (
Subproducts,
Subproducts[Subproduct ID],
Subproducts[Expiration Date]
),
"@Units Inventory", [Units Inventory]
)
VAR GreaterthanZero =
FILTER ( InventoryTable, [@Units Inventory] > 0 ) //Inventory of Subproducts greater than Zero
VAR Result =
SUMX (
GreaterthanZero,
DATEDIFF ( TODAY (), Subproducts[Expiration Date], MONTH )
)
RETURN
IF ( ISINSCOPE ( Products[Product ID] ), Result )
Also I am sure that MAX is getting evaluated in filter context and isn’t using your row context. And if your dataset is huge then that DATEDIFF might slow down the performance.
Sorry for all the questions but I´m trying to get this measure correct for all the possible outcomes.
The only thing I´m missing is to Remove the Date Filters, the measure must stay the same no matter the filters coming from Dates Table.
REMOVEFILTERS is a CALCULATE modifier, and not a table function, you can only use it with CALCULATETABLE and CALCULATE.
MAX Inside an empty (REMOVEFILTERS) filter context will have same date for each row so there is no point in adding separate column for same date.
Using ISINSCOPE inside CALCULATE isn’t useful, ISINSCOPE is used to check if the column is used for grouping(SUMMARIZECOLUMNS which PBI uses to prepare report) in the report and if it is being filtered
I would suggest that you read documentation before using any functions.
