Skip to main content
cancel
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.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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