Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
Hi Angelia,
Don't worry about it - I think I found another solution within our DW.
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |