Calculate where type clause from two different tables

[IoanCosmin]

I haven't written DAX in a while and I'm having a bit of a hard time putting this together and I am hoping someone could throw in a suggestion.

What I have:

Qty Table (a lot more months than January):

+----------+-----------+----------+
| Location |   Date    | LaborQty |
+----------+-----------+----------+
| NY       | 1/3/2017  | 41.024   |
| NY       | 1/4/2017  | 33.836   |
| NY       | 1/5/2017  | 20.431   |
| NY       | 1/6/2017  | 35.544   |
| NY       | 1/7/2017  | 0        |
| NY       | 1/9/2017  | 33.337   |
| NY       | 1/10/2017 | 41.799   |
| NY       | 1/11/2017 | 70.469   |
| NY       | 1/12/2017 | 35.514   |
| NY       | 1/13/2017 | 31.573   |
| NY       | 1/15/2017 | 0        |
| NY       | 1/16/2017 | 22.041   |
| NY       | 1/17/2017 | 30.518   |
| NY       | 1/18/2017 | 47.576   |
| NY       | 1/19/2017 | 29.53    |
| NY       | 1/20/2017 | 18.155   |
| NY       | 1/21/2017 | 0        |
| NY       | 1/23/2017 | 31.284   |
| NY       | 1/24/2017 | 27.695   |
| NY       | 1/25/2017 | 38.907   |
| NY       | 1/26/2017 | 16.289   |
| NY       | 1/27/2017 | 30.976   |
| NY       | 1/28/2017 | 0        |
| NY       | 1/30/2017 | 21.434   |
| NY       | 1/31/2017 | 16.49    |
+----------+-----------+----------+...etc

Rates Table:

+----------+-----------+------------+-----------+---------+-----------+--------+
| Location | DateFrom  |   DateTo   | MonthFrom | MonthTo | RateType  | Amount |
+----------+-----------+------------+-----------+---------+-----------+--------+
| NY       | 1/1/2017  | 6/30/2017  |         1 |       6 | LaborRate | 129.7  |
| NY       | 7/1/2017  | 9/30/2017  |         7 |       9 | LaborRate | 129.8  |
| NY       | 10/1/2017 | 12/31/2017 |        10 |      12 | LaborRate | 129.9  |
| DC       | 1/1/2017  | 6/30/2017  |         1 |       6 | LaborRate | 130.1  |
| DC       | 7/1/2017  | 9/30/2017  |         7 |       9 | LaborRate | 130.5  |
| DC       | 10/1/2017 | 12/31/2017 |        10 |      12 | LaborRate | 130.7  |
+----------+-----------+------------+-----------+---------+-----------+--------+

Desired type of output for the month (e.g. LaborQty x LaborRate):

+-------+----------+-----------+------------+
| Month | LaborQty | LaborRate |   Result   |
+-------+----------+-----------+------------+
|     1 | 674.22   | 129.74    | 87473.3    |
|     2 | 350      | 129.74    | 45409      |
|     3 | 375      | 129.74    | 48652.5    |
|     4 | 400      | 129.74    | 51896      |
|     5 | 380      | 129.74    | 49301.2    |
|     6 | 500      | 129.74    | 64870      |
|     7 | 550      | 129.76    | 71368      |
|     8 | 600      | 129.76    | 77856      |
|     9 | 675      | 129.76    | 87588      |
|    10 | 700      | 129.98    | 90986      |
|    11 | 780      | 129.98    | 101384.4   |
+-------+----------+-----------+------------+

What I am trying to write:

A DAX measure that will output amount like the one shown in the result column. If I where to write a linq query for grabbing the correct rate it will look something like this:

LaborRate = db.Rates
            .Where(a => a.DateFrom <= SelectedDate & a.DateTo >= SelectedDate & a.RateType == "LaborRate")
            .Select(a => a.Amount).Sum();

I have tried a combination of CALCULATE, SUM, FILTER, RELATED, SUMX but I couldn't get it to work. Any advice would be much appreciated. What would be the simplest approach? My conditions/filters are: Location, Date >=DateFrom, Date<=DateTo, RateType. 

[Zubair_Muhammad · Accepted solution]

Hi @IoanCosmin

Please see the attached file here. Hope it helps

Here are the steps

First I added a Caclulated Column in QtyTable

Month =
MONTH ( QtyTable[Date] )

Second, a MEASURE in QtyTable to get the LaborRate for the months

LaborRate =
CALCULATE (
    VALUES ( RatesTable[Amount] ),
    FILTER (
        RatesTable,
        SELECTEDVALUE ( QtyTable[Month] ) >= MONTH ( RatesTable[DateFrom] )
            && SELECTEDVALUE ( QtyTable[Month] ) <= MONTH ( RatesTable[DateTo] )
            && SELECTEDVALUE ( QtyTable[Location] ) = RatesTable[Location]
    )
)

Thirdly a MEASURE to get the RESULT

RESULTT =
SUM ( QtyTable[LaborQty] ) * [LaborRate]

[IoanCosmin]

Hello Zubair,

Thank you so much for putting everything together. This is so nice of you to help and create an example for me. I really appreciate your effort. This indeed solves my problem and it works great!

This is more out of curiosity... I have noticed that you cannot use SELECTEDVALUE in PowerPivot. It's a new function for PowerBI and I was wondering if there's something similar that might work as a replacement. I do no actually need it at this point.

Best,

C

[IoanCosmin]

Hello Zubair,

Thank you so much for putting everything together. This is so nice of you to help and create an example for me. I really appreciate your effort. This indeed solves my problem and it works great!

This is more out of curiosity... I have noticed that you cannot use SELECTEDVALUE in PowerPivot. It's a new function for PowerBI and I was wondering if there's something similar that might work as a replacement. I do no actually need it at this point.

Best,

C

[Zubair_Muhammad]

Hi @IoanCosmin

Yes SelectedValue is a new function. Even I do not have it in Excel 2016

Instead of

SELECTEDVALUE ( Table[column] )

You can also use

IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ) )

[Zubair_Muhammad]

Hi @IoanCosmin

A very good article on SELECTEDVALUE function by the champ @Marco Russo

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

[IoanCosmin]

Hello @Zubair_Muhammad!

You've been of great help. Thanks a lot!

Have a great day,

C