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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
MarieD
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:

MarieD_0-1713948044124.png

This is the model:

MarieD_2-1713948809642.png

 


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?

 MarieD_1-1713948250508.png

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 

 

3 REPLIES 3
lbendlin
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 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...

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

Can I interest you in a graphical solution?

 

lbendlin_0-1714093280369.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.