Apply a Rate

[Centaur]

Hello,

I am a novice user of Power Query. 

I have a problem I cant figure out. 

Grateful for the assistance. 

here is what I need to do : 

1. I need to apply a rate from a rate table and apply that rate to a pivoted Power Query data set
2. The rate is dependent upon 2 criteria:  ProjID and the Date. 
3. I need to apply the rate from the rate table if the record's Date as found in Row 1 (its a header row, pic below) is >[Date From] and <[Date To] in the rate table.  But it must be on [ProjID] match
4. If the records date is in that range and a match on [ProjID] as found on the rate table then apply that rate from the rate table.   

I have 2 Connections in Power Query (the pivoted data set based on the raw excel data and the rate table)

I dont know how I can add the rate to the pivoted data set. 

Maybe its an IF statement?

Below is a link to the file since I cant upload on this website. 

https://1drv.ms/x/s!AkvCjNvcBhpQiPRV5B_j-qzVOgpMhQ?e=PmHewK

the rate table:

this is the pivoted table and I want to append the rate above to this table (according to the criteria):

Any assistance is greatly appreciated.  Thank you

[mussaenda]

Hi @Centaur ,

Please provide a sample data and your desired output.

Thanks

[v-jingzhang · Accepted solution]

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)

Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships, Data Modeling & Much More! -...

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Centaur]

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.