Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I
Helper I

Issue with returning value from previous period when product no longer exists in the current period

Hi all,


I'm working on a report that should compare forecasted values over different Forecasts. The most recent Forecast has an index number 0, previous one is -1, the one before previous -2 and so on.

I need to compare the value from the current forecast with the values from the previous forecast and this is working fine as long as the product keeps existing in the file. But now I have a product that was in FC-4, but from FC-3 it is no longer there. And then the table doesn't show this product anymore, but it should return 0 in FC-3 as ordered and the difference with FC-4:


This is the model:



After searching the forum, I found this post which is more or less about the same issue:

And based on this I wrote my messure like this:





Previous FC 2 = 
VAR current_FC =
    SELECTEDVALUE ( FACT_Forecasts[Index] )
VAR curcust =
    SELECTEDVALUE ( DIM_Customer[Item (DIM).Preferred Customer No.] )
VAR curitem =
    SELECTEDVALUE ( DIM_Item[Item No. Customer] )
VAR previous_visibleDate =
        MAX ( FACT_Forecasts[FC Datum]),
        FILTER (
            ALLSELECTED (FACT_Forecasts ),
             FACT_Forecasts[ArtikelNr]= curitem
                && FACT_Forecasts[Klant] = curcust
                && FACT_Forecasts[Index] < current_FC
                && [# Ordered FC] <> BLANK ()

VAR previous_visibleDateNumber =
        [# Ordered FC],
        FILTER (
            ALLSELECTED ( FACT_Forecasts ),
                FACT_Forecasts[ArtikelNr] = curitem
                && FACT_Forecasts[Klant] = curcust
                && FACT_Forecasts[FC Datum] = previous_visibleDate




But it is still not giving the result that I want because it is collapsing my table and returning values in rows that should not be shown. The forecast table has the forecast for several customers, and for each customer, the most recent Forecast = 0. So FC index 0 is not a unique value in this table, but I always use it in combination with a selected customer and this should make it unique. So ot sure if this is causing the problem?


So I think that i'm almost there, but just don't know how to solve the last part.. It would be great it somebody can help me! 
i have a dummy version of the report available via this WeTransfer link: 


Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to

Please show the expected outcome based on the sample data you provided.

Want faster answers?

Hi, Thanks you for your reply.
I have updated my post now with a Wetransfer link for a dummy version of the report

Can I interest you in a graphical solution?




Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.