Power query - build table of cost per day from a list of quotes with start and end dates

[jpc]

I am trying to use powerQuery to build a table of costing information that I can use with a common date table.  

I have a table of quotes, with start and end dates.

I also have two parameter in powerQuery, StartOfRange and EndOfRange

Basically, i want to build a table of the cost of each product per day, between StartOfRange and EndOfRange.   Where quote information does not exist, the closest known value persists back or forwrd in time up to the StartOfRange and EndOfRange limits.  This ensures that whatever date the user selects, they get a numerical value of cost for an item.  

Example data: 

Quote	Item	Start Date	End Date	Cost
One	Alpha	14/01/2020	22/01/2020	1.11
One	Beta	14/01/2020	22/01/2020	2.22
One	Gamma	14/01/2020	22/01/2020	3.33
Two	Alpha	23/01/2020	27/01/2020	1.77
Two	Beta	23/01/2020	27/01/2020	2.88

Desired output: 

(for parameters StartOfRange=1/1/2020 and EndOfRange=30/1/2020 and only shown for one Item to illustrate the point)

Item	Quote	Date	Cost
Alpha		01/01/2020	1.11
Alpha		02/01/2020	1.11
Alpha		03/01/2020	1.11
Alpha		04/01/2020	1.11
Alpha		05/01/2020	1.11
Alpha		06/01/2020	1.11
Alpha		07/01/2020	1.11
Alpha		08/01/2020	1.11
Alpha		09/01/2020	1.11
Alpha		10/01/2020	1.11
Alpha		11/01/2020	1.11
Alpha		12/01/2020	1.11
Alpha		13/01/2020	1.11
Alpha	One	14/01/2020	1.11
Alpha	One	15/01/2020	1.11
Alpha	One	16/01/2020	1.11
Alpha	One	17/01/2020	1.11
Alpha	One	18/01/2020	1.11
Alpha	One	19/01/2020	1.11
Alpha	One	20/01/2020	1.11
Alpha	One	21/01/2020	1.11
Alpha	One	22/01/2020	1.11
Alpha	Two	23/01/2020	1.77
Alpha	Two	24/01/2020	1.77
Alpha	Two	25/01/2020	1.77
Alpha	Two	26/01/2020	1.77
Alpha	Two	27/01/2020	1.77
Alpha		28/01/2020	1.77
Alpha		29/01/2020	1.77
Alpha		30/01/2020	1.77

How should i approach this in M/power query?

So far, i can fill in the gaps using syntax like  {[Start Date]..[End Date]}   for each line in the quote table, but i am stuck adding the rows for the before and after times where there is no quote coverage

Thank you in advance!

[Ashish_Mathur]

Hi,

Alpha (whether One or Two), starts from Jan 14.  In your result, why should there by any data from Jan 1 to Jan 13?  Also, if you want to control the result with a slicer, then your should be writing a measure to solve this question (rather then runing an M code).

[jpc]

Hi, could you elaborate what the measure/slicer solution might look like?   I'm open to approaching this in a different way - essentially i want to be able to view cost bulidup as a function of time (cost history, cost roadmap) so if a measure approach is best practice, this could be useful insight to me and others.

[Ashish_Mathur]

Please answer the question which i asked in my previous message.  If my understand is correct as per the question asked, then show the revised result. 

[jpc]

The reason for populating 1-13th January is because when we don't always have cost information for all date range, i don't really want zero cost if the user selects a date in the past, so closest known data is better than none, but I do plan to create a warning flag for these cases.   I can understand your point though, there is not data here, so i should not create it.   I think the case for 28-30th is more valid - the quote range ended, so the assumption is that the cost remains the same unless we get a new quote, although i would again create a flag or colour coding to indicate that cost is baesd on expired quote

Happy to hear a solution with a different assumption regarding early cost information - I could address this in another way (ask for historical costs to be loaded)

Thank you for your time, i look forwrad to hearing of another approach

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.  See if it helps.

[jpc]

Thank you - this is interesting and helpful example - i am still working through applying / extending to my problem, but i wanted to post an interim update

Using this measure, i can build the basic functionality as follows:

CostToUse = MAXX(FILTER('CostData','CostData'[Start Date]<MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date])) , 'CostData'[Cost])

Giving the following output

I'm not sure yet how this DAX can be changed to extend the cost value beyond the end date of the last available quote - ie through to the 30/1 date?    (I got the correct functionality for "Alpha" by removing the second half of the FILTER clauses that checked for end date, but then realised that this only works in the case that costs always go up, due to the use of MAXX, when i put a cost reduction in the source data for Beta, it doesn't work)

Any suggestion welcome, i'll post back here if i make progress myself

[jpc]

Adding my solution here for completeness - maybe not the most elegant but it works

I will not mark this as a solution as it does not solve the original task of building a table of all dates in M/power query

CostToUse = 

VAR __CostIfAvail = MAXX(FILTER('CostData','CostData'[Start Date]<=MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date]) ) , 'CostData'[Cost])  

VAR __LatestEnd = MAX( CostData[End Date] )
VAR __EarliestStart = MIN( CostData[Start Date] )

VAR __CostEarliest = MAXX(FILTER('CostData','CostData'[Start Date]= __EarliestStart)  , 'CostData'[Cost])
VAR __CostLatest = MAXX(FILTER('CostData','CostData'[End Date]= __LatestEnd)  , 'CostData'[Cost])

VAR __CostInferred = IF( MAX('Calendar'[Date]) < __EarliestStart, __CostEarliest, __CostLatest)

RETURN IF( __CostIfAvail = BLANK() , __CostInferred , __CostIfAvail  )

[jpc · Accepted solution]

...replying again to my own thread in case it is useful to somebody else

I extended my use case a bit to make it robust, and hence had to update my DAX
Other scenarios i wanted to cover - overlapping quotes, and gaps in quotes.  The below addresses all of these now to my satisfaction.

FirmCost = // the cost strictly according to quotes - is blank if no quote is in range

VAR __StartDateOfCostToUse = MAXX(FILTER('CostData','CostData'[Start Date]<=MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date]) ) , 'CostData'[Start Date])  

RETURN MAXX(FILTER('CostData','CostData'[Start Date]=__StartDateOfCostToUse  ) , 'CostData'[Cost])  // positive match

and

SelectedCost =

VAR __CostInBetween = LASTNONBLANKVALUE(    

        FILTER(         
            ALL('Calendar'[Date]), 
            'Calendar'[Date]<=MAX('Calendar'[Date])    
        ), 
        [FirmCost]      

    )

VAR __EarliestStartDate = MIN( CostData[Start Date] )
VAR __EarliestCost = MAXX(FILTER('CostData','CostData'[Start Date]= __EarliestStartDate)  , 'CostData'[Cost])

RETURN if(__CostInBetween = BLANK(), __EarliestCost, __CostInBetween )

[v-rongtiep-msft]

Hi @jpc ,

Please refer to my pbix file to see if it helps you.

Create a date table includes the date from 2020/1/1 to 2020/1/30.

Then merge the 2 tables.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCizNL0lV0lHyLEnNBVLBJYlFJQouiWAx17wUGNM5v7hEKVYnWsk/D8R1zCnISATSRgZGBvqG+oYmCLaREZBtqGdoiKTcKbUEn2ojPSCFUO2emJuLT7mxnrExWHlIeT6mW4yMkdjmYLeYmyMpR3ULhmojPQsLpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start Date", type date}, {"End Date", type date}, {"Cost", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Start Date"}, date, {"date"}, "Table (5)", JoinKind.RightOuter),
    #"Expanded Table (5)" = Table.ExpandTableColumn(#"Merged Queries", "Table (5)", {"date"}, {"Table (5).date"}),
    #"Filled Up" = Table.FillUp(#"Expanded Table (5)",{"Cost", "End Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"End Date", "Start Date"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Table (5).date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Table (5).date", "date"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"date", "Cost", "Item"}),
    #"Filled Up1" = Table.FillUp(#"Filled Down",{"Item"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Up1",{"Item", "Quote", "Cost", "date"})
in
    #"Reordered Columns"

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.