Forum Discussion

chris_m's avatar
chris_m
Helper I
8 years ago
Solved

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?

 

 

Need Help
  • chris_m's avatar
    chris_m
    8 years ago

    Hi Angelia,

     

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

     

     

5 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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.



    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.



    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]
)



    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

     

    • chris_m's avatar
      chris_m
      Helper I
      8 years ago

      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.