Forum Discussion

jpc's avatar
jpc
Icon for Helper I rankHelper I
2 years ago
Solved

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

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 pow...
Need Help
  • jpc's avatar
    jpc
    2 years ago

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

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

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's avatar
jpc
Icon for Helper I rankHelper I
2 years ago

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

    • jpc's avatar
      jpc
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        jpc
        Icon for Helper I rankHelper I
        2 years ago

        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  )