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

[MarieD]

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: 
https://community.fabric.microsoft.com/t5/Desktop/Compare-price-previous-non-continuous-date/td-p/24...

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 =
    CALCULATE (
        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 =
    CALCULATE (
        [# Ordered FC],
        FILTER (
            ALLSELECTED ( FACT_Forecasts ),
                FACT_Forecasts[ArtikelNr] = curitem
                && FACT_Forecasts[Klant] = curcust
                && FACT_Forecasts[FC Datum] = previous_visibleDate
        )
    )
RETURN
    previous_visibleDateNumber

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:  https://we.tl/t-jjguNL3232 

[lbendlin]

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 https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

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

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MarieD]

Hi, Thanks you for your reply.
I have updated my post now with a Wetransfer link for a dummy version of the report
https://we.tl/t-jjguNL3232

[lbendlin]

Can I interest you in a graphical solution?