Forum Discussion

lekkerbek's avatar
lekkerbek
Icon for Helper IV rankHelper IV
7 years ago
Solved

Apply rate that is between 2 dates

Hi,   I'm having trouble writing a Dax code or perhaps I should be using a new column.   I have a dataset with the following tables: - Employees with internal rate that vary each month, so for e...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    lekkerbek

     

    You can import the rate into the fact table.

    First you need to replace the blank values (with Power Query) within the Rates table (column End Date) with a big date (for example 12/31/9999).

    Then you can add a column in the fact table with this DAX:

     

    Rate = 
CALCULATE(
    VALUES( Rates[InternalRate] ),
    Rates[StartDate] <= EARLIER(Data[Date]),
    Rates[EndDate] >= EARLIER(Data[Date]),
    Rates[EmployeeID] = EARLIER(Data[EmployeeID])
)

     

     

    then your projection costs can be calculated on the fly without needing to add a calculated column for those. I would recommend to add the calculated column with the Rate as I did and not multiply it by Quantity within the calculated column in order to get a better compression (Rates have less unique values). If you're extracting data from a database then you can join the fact table to the Rates table in order to retrieve the correct rate:

     

    SELECT FactTable.*, Rates.Rate

    FROM FactTable INNER JOIN Rates

    ON FactTable.EmployeeID = Rates.EmployeeID AND FactTable.Date BETWEEN Rates.StartDate and Rates.EndDate

     

     

    Another alternative can also be to create the below model and not add the calculated column with the Rate but retrieve at query time but I think you will get a poorer performance so I'd go with the first option.

     

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

Hi lekkerbek

 

can the rate vary by Employee? Could you post a better sample in tabular format?

 

thx

  • lekkerbek's avatar
    lekkerbek
    Icon for Helper IV rankHelper IV
    7 years ago

    Hi LivioLanzo,

     

    Yes the rates vary and the current month has no enddate. It could be that the rate doesn't change for the next month. The two tables look like this:

     

    The projects costs (in the project time table) is obviously the desired outcome of the formula which is currently blank in my table. Formula is quantity x internal rate (but depends on the employee and the date).

     

    Employee (the end date in December 2018 is blank intentionally):

     

    EmployeeIDStartDateEndDateInternalRate
    11-1-201831-1-2018 €           71,00
    11-2-201828-2-2018 €           71,10
    11-3-201831-3-2018 €           71,20
    11-4-201830-4-2018 €           71,30
    11-5-201831-5-2018 €           71,40
    11-6-201830-6-2018 €           71,50
    11-7-201831-7-2018 €           71,60
    11-8-201831-8-2018 €           71,70
    11-9-201830-9-2018 €           71,80
    11-10-201831-10-2018 €           71,90
    11-11-201830-11-2018 €           72,00
    11-12-2018  €           72,10
    21-1-201831-1-2018 €           50,00
    21-2-201828-2-2018 €           51,00
    21-3-201831-3-2018 €           52,00
    21-4-201830-4-2018 €           53,00
    21-5-201831-5-2018 €           54,00
    21-6-201830-6-2018 €           55,00
    21-7-201831-7-2018 €           56,00
    21-8-201831-8-2018 €           57,00
    21-9-201830-9-2018 €           58,00
    21-10-201831-10-2018 €           59,00
    21-11-201830-11-2018 €           60,00
    21-12-2018  €           61,00

     

    Project time:

     

    ActivityDescriptionCurrencyDateEmployeeIDProjectCodeProjectDescriptionQuantityProject costs
    WP7 - RP2 - HoursEUR31-8-20181500Project A28,5 €     2.043,45
    WP2 - RP2 - HoursEUR28-2-20181600Project B40,01 €     2.844,71
    WP1 - RP1 - HoursEUR30-4-20172700Project C8,6 €         455,80
    WP1 - RP2 - HoursEUR30-4-20182800Project D3 €         159,00
    • LivioLanzo's avatar
      LivioLanzo
      Icon for Solution Sage rankSolution Sage
      7 years ago

      lekkerbek

       

      You can import the rate into the fact table.

      First you need to replace the blank values (with Power Query) within the Rates table (column End Date) with a big date (for example 12/31/9999).

      Then you can add a column in the fact table with this DAX:

       

      Rate = 
CALCULATE(
    VALUES( Rates[InternalRate] ),
    Rates[StartDate] <= EARLIER(Data[Date]),
    Rates[EndDate] >= EARLIER(Data[Date]),
    Rates[EmployeeID] = EARLIER(Data[EmployeeID])
)

       

       

      then your projection costs can be calculated on the fly without needing to add a calculated column for those. I would recommend to add the calculated column with the Rate as I did and not multiply it by Quantity within the calculated column in order to get a better compression (Rates have less unique values). If you're extracting data from a database then you can join the fact table to the Rates table in order to retrieve the correct rate:

       

      SELECT FactTable.*, Rates.Rate

      FROM FactTable INNER JOIN Rates

      ON FactTable.EmployeeID = Rates.EmployeeID AND FactTable.Date BETWEEN Rates.StartDate and Rates.EndDate

       

       

      Another alternative can also be to create the below model and not add the calculated column with the Rate but retrieve at query time but I think you will get a poorer performance so I'd go with the first option.

       

      • lekkerbek's avatar
        lekkerbek
        Icon for Helper IV rankHelper IV
        7 years ago

        Thank you very much LivioLanzo. It works like a charm!

         

        :-)