Forum Discussion

SantiYegros's avatar
SantiYegros
Frequent Visitor
2 years ago
Solved

Measure, Calculated column or Table to solve this

Hello, I am working with data from cattleraising. I am currently facing a challenge and haven't been able to work around it. I have a two tables, Table 1 with 3 columns, [ID], [Date] and [Kg]. The other table is a Calendar table. On Table 1 there are several Dates for when [ID] have been weighted throughout the year.
I have created a measure called [GDP] to calculate the wieght gained per day between one Date and the next for each [ID]. Now i am trying to to find the average of [GDP] of all [ID] for each day in the Calendar table, regardless of each [ID] having a Date in Table1. 
Example of Table 1

DateIDKg
15/01/202400001150
20/01/202400001155
25/01/202400001165

[GDP] of ID 00001 between dates 1 and 2 is 1 kg/day where GDP is (155-150)/(5) 
[GDP] of ID 00001 between dates 2 and 3 is 2 kg/day where GDP is (165-155)/(5) 
Example of table needed and don't know how to get it:

DateGDP
16/01/20241
17/01/20241
18/01/20241
19/01/20241
20/01/20241
21/01/20242
22/01/20242
23/01/20242
24/01/20242
25/01/20242

If anyone can help, thanks in advanced!

Need Help
Tips and Tricks

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    This is a standard intrapolation.

     

    GPD = 
var d = SELECTEDVALUE('Calendar'[Date])
var pd = maxx(filter('Table',[Date]<d),[Date])
var nd = minx(filter('Table',[Date]>=d),[Date])
var pv = maxx(filter('Table',[Date]=pd),[Kg])
var nv = maxx(filter('Table',[Date]=nd),[Kg])
return if(not ISBLANK(pd) && not ISBLANK(nd),pv + DIVIDE(nv-pv,nd-pd,0)*(d-pd))
    • SantiYegros's avatar
      SantiYegros
      Frequent Visitor
      2 years ago

      Sorry, I didn't explain myself thouroghly. 
      I have created a sample dataset to try and explain myself better. This is a visual from my sample table.

      I need a way to average the GDP for the months (or days) in between the data as well. GDP would be the change of KG between the weighting days. I need to get to this:

       

      Where the cells in yellow is the GDP but for dates/months where there is no data. In the visual we can see there is no data for december 2023, so the GDP for that ID from January 24 is reflected in dec 23, because that ID had a change of KG of 0,400 per day. So the average per month would indeed reflect the change for every ID for every month.

       

      Thanks for the patience and the help!

       

      This is the link for my sample data:
      SAMPLEPOWERBI

  • SantiYegros's avatar
    SantiYegros
    Frequent Visitor
    2 years ago

    You are absolutely right!
    Thanks. 
    I created a new table instead of using as measure, so I can relate to a calendar table and work with it. Thanks lbendlin