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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chris_m
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
v-huizhn-msft
Employee
Employee

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. 

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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