Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Linear Interpolation between dates and another dimension

Hi Everyone,     Im trying to replicate this report in Power BI    http://ir.eia.gov/ngs/ngs.html   I found the raw data for free via Odata which is great as I can auto refresh   Data Source...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Anonymous - Glad you liked that post. So, it seems that you are running into an issue because you need this to be dynamic (measure) and not calculated columns?

 

If you could maybe post some sample data and expected output that would be very helpful. 

 

Also, I did another similar post here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mind-the-Gap-Irregular-Time-Series/m-p/991790#M436

 

I do not see why this kind of thing couldn't be done "on the fly", essentially constructing the interpolation table as a table variable in DAX but I'd have to understand exactly what your expectations are.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, 

     

    Thank you for your quick response, this is the sample data

    https://we.tl/t-Gyr0Z51QH3

    and this is the pibx file

    https://we.tl/t-kU8RumZUSh

     

     

    I guess my where I am confused is would I have to create a date table with each day of year repeating itself each occurence of the dimension to showcase daiyly volumes per region? 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Actually might be best to use the pbix file as the data from the sample file is pivoted. Greg_Deckler 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I try Greg_Deckler

         

         

        Maybe the way they are calculating it its different? 

         

        http://ir.eia.gov/ngs/methodology.html

         

         

        This is my DAX.

         

        Am I doing something wrong?

         

        Interpolated Value =
        VAR x3 = MAX('Date'[Date])
        VAR match = CALCULATE(MAX('Working Gas in Underground Storage (W)'[VALUE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]=x3))
        VAR x1 = CALCULATE(MAX('Working Gas in Underground Storage (W)'[DATE]),FILTER('Working Gas in Underground Storage (W)', 'Working Gas in Underground Storage (W)'[DATE]<=x3))
        VAR x2 = CALCULATE(MIN('Working Gas in Underground Storage (W)'[DATE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]>=x3))
        VAR y1 = CALCULATE(MAX('Working Gas in Underground Storage (W)'[VALUE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]<=x3))
        VAR y2 = CALCULATE(MIN('Working Gas in Underground Storage (W)'[VALUE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]>=x3))
        RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1))