Product Life (DateDiff) Virtual Table

Hello Guys,

Happy New Year to Y’all !!!

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:
image

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.
image

image

I need to get these values in a Measure, not in a Calculated Column though.

Thanks for the help!!!

@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.

Thanks @AntrikshSharma that really helps.

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.

image
image

@Yeriel

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.

2 Likes

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.

  1. Attempt:

  2. Attempt

Also, I took into consideration the below comment and Added the Max measure to reduce the slow performance.

@Yeriel -

  1. REMOVEFILTERS is a CALCULATE modifier, and not a table function, you can only use it with CALCULATETABLE and CALCULATE.
  2. 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.
  3. 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.

1 Like

Thanks a lot for the explanation and guidance @AntrikshSharma

So how can I remove any Filter coming from the Dates Table with the current measure?

Product Life =
VAR InventoryTable =
ADDCOLUMNS (
SUMMARIZE (
Subproducts,
Subproducts[Subproduct ID],
Subproducts[Expiration Date]
),
@Units Inventory”, [Units Inventory],
“Max Expiration Date”, MAX( Subproducts[Expiration Date] )
)
VAR GreaterthanZero =
FILTER ( InventoryTable, [@Units Inventory] > 0 )

VAR VtDateDiff =
SUMX (
GreaterthanZero,
DATEDIFF ( TODAY (), [Max Expiration Date], DAY )
)
VAR Result =
DIVIDE( VtDateDiff, 30, 0 ) // To return Months with decimals
RETURN

IF ( ISINSCOPE ( Subproducts[Subproduct ID] ), Result )

@Yeriel CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( …, REMOVEFILTERS ( Dates ) )

1 Like

You are amazing @AntrikshSharma !!!

Thank you Thank you Thank you!!!

:clap: :clap: :clap: :clap: :clap: :clap: :clap:

1 Like