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 ot...
Need Help
Tips and Tricks
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