Forum Discussion
Apply a Rate
Hi Centaur
You can create a measure to get the rate according to project ID and dates periods. Put this measure into Values of your pivot table. I create the following sample with Power BI. It's similar in Excel's Power Pivot table.
m_Rate = var __date = MAX('data'[dte]) var __projID = MAX('data'[ProjID]) return CALCULATE(MAX(Rates[Rate]),'Rates'[ProjectID]=__projID,'Rates'[Date From]<=__date,Rates[Date To]>__date)Reference:
Measures in Power Pivot (microsoft.com)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi Centaur
You can create a measure to get the rate according to project ID and dates periods. Put this measure into Values of your pivot table. I create the following sample with Power BI. It's similar in Excel's Power Pivot table.
m_Rate =
var __date = MAX('data'[dte])
var __projID = MAX('data'[ProjID])
return
CALCULATE(MAX(Rates[Rate]),'Rates'[ProjectID]=__projID,'Rates'[Date From]<=__date,Rates[Date To]>__date)
Reference:
Measures in Power Pivot (microsoft.com)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi, thank you for the response. I will try to implement but I want to ask a question first. After looking at the header row, the dates are not in order in my sample file posted. you can see they are out of order if you scroll all the way to the right. I am not sure why PQ is sorting in this fashion. I need all of the dates in order from left to right. Do you see what i am referring to? do you happen to know why it does this? I have resorted to using excel but I would like to have a PQ solution as well.
here is a screen shot
must scroll all way to the right.