cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculating Rolling Locked Forecast Values

HI,

 

I am trying to calculate a rolling locked forecast measure and I'm stuck. I am trying to calculate a locked forecast value for the forecast that we receive from one of our customers. 

We receive a new forecast each week, but we can receive multiple versions of that forecast during a week (or even on a day). For each week, I want to identify the latest version, and use that to determine the forecast volume for a period in the future (6 weeks / or 42 days)

My progress so far:

 

Received Week End Date =
 ( 'Customer Forecast'[Forecast Received Date] + 7 )
    - WEEKDAY ( 'Customer Forecast'[Forecast Received Date], 2 )
//Calculates the end of week date that the forecast was received
Lock Week Date =
'Customer Forecast'[Received Week End Date] + 42
//Sets the lock week date (6 weeks ahead)
Locked Customer Forecast 1 =
CALCULATE (
    [Sum Customer Forecast],
    FILTER (
        'Customer Forecast',
        'Customer Forecast'[ETDDate] = 'Customer Forecast'[Lock Week Date]
    )
)
//Calculates the customer forecast value where the ETD is in the locked week

Now here is where I get stuck. I need to calculate 'Locked Customer Forecast 1' for the latest version of the forecast received. 

The forecast versions are labelled like this: (syntax = YYYYMMDD-### where the ### changes if more than one forecast is received on a single day)

20170927-001
20170929-007
20171003-001
20171004-001
20171011-001
20171012-001
20171019-001
20171027-001
20171101-001
20171102-001
20171102-002
20171108-001
20171109-001

 

This doesnt work, it only returns the very last forecast version ID. 

Locked Customer Forecast 2 =
CALCULATE (
    [Locked Customer Forecast 1],
    FILTER (
        'Customer Forecast',
        'Customer Forecast'[Customer Forecast Version ID - Copy]
            = MAX ( 'Customer Forecast'[Customer Forecast Version ID - Copy] )
    )
)

If I want to calculate this in the context of weeks in my date table, how do I do it?

 

 

1 ACCEPTED SOLUTION

Hi Angelia,

 

Don't worry about it - I think I found another solution within our DW. 

 

 

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

Hi @chris_m,

Based on your information, I can only get the latest version for each week by following the steps.

1. I type the forecast versions, and split the column into two column in query editor, please see my Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCoAwEEPRu3StkERhOmcpvf81nIUUDd0+PvwxmsBAKk6AbR4LsiAWELh+RcFtQDrIIQ1sy0octAN9oXvxXuYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [version = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"version", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [version]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.2", "number"}})
in
    #"Renamed Columns"


You will get the following structure data.

1.PNG

2. Transfer the custom.1 column to date format and create several calculated using DAX formulas below.

Date = DATE(LEFT(Forecast[Custom.1],4),RIGHT(LEFT(Forecast[Custom.1],6),2),RIGHT(Forecast[Custom.1],2))

weeknum = WEEKNUM(Forecast[Date])

Latest_number =
CALCULATE (
    MAX ( Forecast[number] ),
    FILTER (
        Forecast,
        Forecast[weeknum] = EARLIER ( Forecast[weeknum] )
            && Forecast[Date] = EARLIER ( Forecast[Date] )
    )
)


lastest_date in one week =
CALCULATE (
    MAX ( Forecast[Date] ),
    FILTER ( Forecast, Forecast[weeknum] = EARLIER ( Forecast[weeknum] ) )
)


You will get the following result.

2.PNG

3. Please click "New Table" under modeling on home page, you will get the latest version each week.

New =
SELECTCOLUMNS (
    FILTER (
        Forecast,
        Forecast[Date] = Forecast[lastest_date in one week]
            && Forecast[number] = Forecast[Latest_number]
    ),
    "weeknumber", Forecast[weeknum],
    "latest_version", Forecast[version]
)

3.PNG

In addition, you said you use latest veriosn of each week to determine the forecast volume for a period in the future (6 weeks / or 42 days), I am confused about your this requirement, what's the calculation rules? Could you please share more details for further analysis?

Best Regards,
Angelia

 

Hi @v-huizhn-msft

 

Thanks for your reply! Is it alright if I send you some data in a private message? 

I may need to remove some sensitive information, but I'll keep the relevant information there. 

 

 

Hi @chris_m,

The link you shared in private message is invalid.

Best Regards,
Angelia

Hi Angelia,

 

Don't worry about it - I think I found another solution within our DW. 

 

 

View solution in original post

Hi @chris_m,

Congratulations, welcome to share your solution and mark the useful reply as answer. More members will benefit from here.

Thanks,
Angelia

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors