Trying to use measures to calculate rates based on date

[ElieK]

Hello,

The organisation I work at recently changed the rates based on the team doing the work, I've been trying to figure out how to use a measure to calculate the revenue based on the new rate.

The old rates before 01-Aug-2020

Team A	150
Team B	75
Team C	80

The new Rates after 01-Aug-2020

Team A	170
Team B	85
Team C	100

The measure I am using is:

Team A Revenue = CALCULATE(TeamTracker[Volume]*150,TeamTracker[Team Name]="Team A")

I tried enclossing the Calculate function in an IF Statement but could not figure out how to look at the Date column in the 'TeamTracker' table.

I tried creating a new Measure for the Team A Rate and using

Team A Rate = IF (Today() < Date(2020,08,01) , 150, 170)

But that just ends up taking the new value, and I cant figure out how to replace the Today() with a date value from either my TeamTracker table or Rolling Calendar table

Thanks

[v-rzhou-msft · Accepted solution]

Hi @ElieK 

I build three table to have a test.

Old Rate Table:

New Rate Table:

TeamTracker Table:

Then I build a measure to achieve your goal.

Revenue = 
VAR _VOLUME =
    SUM ( TeamTracker[Volume] )
VAR _OLDRATE =
    CALCULATE (
        SUM ( 'Old Rates'[Rate] ),
        FILTER ( 'Old Rates', 'Old Rates'[Team] = MAX ( TeamTracker[Team] ) )
    )
VAR _NEWRATE =
    CALCULATE (
        SUM ( 'New Rates'[Rate] ),
        FILTER ( 'New Rates', 'New Rates'[Team] = MAX ( TeamTracker[Team] ) )
    )
RETURN
    IF (
        MAX ( TeamTracker[Date] ) < DATE ( 2020, 08, 01 ),
        _VOLUME * _OLDRATE,
        _VOLUME * _NEWRATE
    )

Result:

You can download the pbix file from this link: Trying to use measures to calculate rates based on date

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[ElieK]

Thanks @v-rzhou-msft  this helped a lot

[amitchandak]

@ElieK , share the data with that you want to use the rate.

New column = Switch( True(),

[Date] <=date(2018,01,01) && [Team] ="A" , [Volume]*150,

[Date] > date(2018,01,01) && [Team] ="A" , [Volume]*170,

/// Add other codition

[ElieK]

Thanks! Is using a Calculated column the best way of doing this?